Skip to content

Latest commit

 

History

History
67 lines (42 loc) · 2.38 KB

sql-assignment-1.md

File metadata and controls

67 lines (42 loc) · 2.38 KB

SQL Assignment 1

As the data analyst for the fulfillment team, your task is to create SQL queries to answer specific business questions. Use the company's data model to retrieve accurate data that will help optimize shipping, logistics, and revenue tracking.

Task Categories

1. Shipment Analysis

Provide SQL queries for the following:

  • Total Shipments in January 2022 (Q1):

    • Find the total number of shipments made in January 2022.

  • Shipments by Tracking Number:

    • List all shipments grouped by their unique tracking numbers.

  • Average Shipments per Month (Q1 2022):

    • Calculate the average number of shipments made per month in Q1 2022.

  • Payment Captured but Not Shipped:

    • Identify orders where payment has been captured but no shipment has been made yet.

  • Multi-Item Orders (Single Ship Group):

    • Find orders where multiple items were shipped together in a single shipment group.

  • Brokered but Not Shipped Orders:

    • Identify orders that have been brokered but not yet shipped.

  • Orders Completed Hourly:

    • Provide a breakdown of completed orders on an hourly basis.

2. Location-Based Fulfillment Metrics

  • Shipped Units by Location:

    • Generate a report showing the number of units shipped from each fulfillment center or store location.

  • Maximum Units Fulfilled by Location:

    • Identify the location that fulfilled the most units in a given period.

  • Total Shipment Value from Facilities 904 and 906 (Q1 2022):

    • Calculate the total value of shipments made from facilities 904 and 906 in Q1 2022.

3. Store-Specific Financial Insights

  • Facility-Wise Revenue:
    • Break down the revenue generated by each fulfillment facility or store.

4. Financial Metrics for Fulfillment

  • Shipping Refunds (Last Month):

    • Calculate the total value of shipping refunds issued last month.

  • Shipping Revenue (Last Month):

    • Calculate the total shipping revenue generated in the last month.

5. Historical Orders and Items

  • Last Week's Imported Orders & Items:

    • Identify and count the orders and items imported last week.

  • Send Sale Orders Shipped from Warehouse:

    • Report on send sale orders that have been shipped from the warehouse.

6. BOPIS (Buy Online, Pick-Up In Store) Metrics

  • BOPIS Orders Revenue (Last Year):
    • Calculate the total revenue generated from BOPIS orders over the past year.