Skip to content

Welcome! You are now in DataLab.

You successfully completed your project and are looking for some additional related challenges. This DataLab workbook contains the official solution from our curriculum staff, along with Additional Challenges at the bottom. If you would like a quick overview of DataLab, please refer to the help menu. You can easily share your project with your friends and colleagues when you're done.

Good luck with your additional challenges!

You're working for a company that sells motorcycle parts, and they've asked for some help in analyzing their sales data!

They operate three warehouses in the area, selling both retail and wholesale. They offer a variety of parts and accept credit cards, cash, and bank transfer as payment methods. However, each payment type incurs a different fee.

The board of directors wants to gain a better understanding of wholesale revenue by product line, and how this varies month-to-month and across warehouses. You have been tasked with calculating net revenue for each product line and grouping results by month and warehouse. The results should be filtered so that only "Wholesale" orders are included.

They have provided you with access to their database, which contains the following table called sales:

Sales

ColumnData typeDescription
order_numberVARCHARUnique order number.
dateDATEDate of the order, from June to August 2021.
warehouseVARCHARThe warehouse that the order was made from— North, Central, or West.
client_typeVARCHARWhether the order was Retail or Wholesale.
product_lineVARCHARType of product ordered.
quantityINTNumber of products ordered.
unit_priceFLOATPrice per product (dollars).
totalFLOATTotal price of the order (dollars).
paymentVARCHARPayment method—Credit card, Transfer, or Cash.
payment_feeFLOATPercentage of total charged as a result of the payment method.

Your query output should be presented in the following format:

product_linemonthwarehousenet_revenue
product_one---------
product_one---------
product_one---------
product_one---------
product_one---------
product_one---------
product_two---------
............
Spinner
DataFrameas
revenue_by_product_line
variable
-- Start coding here
SELECT product_line,
    CASE WHEN EXTRACT('month' from date) = 6 THEN 'June'
        WHEN EXTRACT('month' from date) = 7 THEN 'July'
        WHEN EXTRACT('month' from date) = 8 THEN 'August'
    END as month,
    warehouse,
	SUM(total) - SUM(payment_fee) AS net_revenue
FROM sales
WHERE client_type = 'Wholesale'
GROUP BY product_line, warehouse, month
ORDER BY product_line, month, net_revenue DESC

The finance team is exploring ways to reduce transaction costs and improve profitability. They’ve asked you to determine the most profitable payment method for each warehouse in each month. Calculate the net revenue for each payment method, grouped by warehouse and month, and identify the top payment method for each combination.

Spinner
DataFrameas
revenue_by_payment_method
variable
WITH net_revenue_by_payment AS (
    SELECT
        CASE WHEN EXTRACT('month' from date) = 6 THEN 'June'
        WHEN EXTRACT('month' from date) = 7 THEN 'July'
        WHEN EXTRACT('month' from date) = 8 THEN 'August'
    END as sales_month,
        warehouse,
        payment,
        SUM(total - (total * payment_fee / 100)) AS net_revenue
    FROM sales
    GROUP BY
        sales_month,
        warehouse,
        payment
),
ranked_payments AS (
    SELECT
        sales_month,
        warehouse,
        payment,
        net_revenue,
        RANK() OVER (
            PARTITION BY sales_month, warehouse
            ORDER BY net_revenue DESC
        ) AS revenue_rank
    FROM net_revenue_by_payment
)
SELECT
    sales_month,
    warehouse,
    payment AS most_profitable_payment_method,
    net_revenue
FROM ranked_payments
WHERE revenue_rank = 1
ORDER BY sales_month, warehouse;

The marketing team is planning a targeted campaign and wants to know the most popular product lines for retail and wholesale customers.

They have given you the task to find the top 3 most ordered product lines for each client type.

Spinner
DataFrameas
top_ordered_product_lines
variable
WITH product_totals AS (
    SELECT
        client_type,
        product_line,
        SUM(quantity) AS total_quantity_ordered
    FROM sales
    GROUP BY
        client_type,
        product_line
),
ranked_products AS (
    SELECT
        client_type,
        product_line,
        total_quantity_ordered,
        RANK() OVER (
            PARTITION BY client_type
            ORDER BY total_quantity_ordered DESC
        ) AS product_rank
    FROM product_totals
)
SELECT
    client_type,
    product_line,
    total_quantity_ordered,
    product_rank
FROM ranked_products
WHERE product_rank <= 3
ORDER BY client_type, product_rank;