Skip to content
Project: Analyzing Motorcycle Part Sales
  • AI Chat
  • Code
  • Report
  • 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.
    Unknown integration
    DataFrameavailable as
    revenue_by_product_line
    variable
    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,
      ROUND((SUM(total) * (1 - payment_fee))::numeric,2) AS net_revenue
    FROM sales
    WHERE client_type IN ('Wholesale')
    GROUP BY product_line, month, warehouse, payment_fee
    ORDER BY product_line ASC, month ASC, net_revenue DESC;
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
    Unknown integration
    DataFrameavailable as
    df
    variable
    -- Comparing the net revenue from Wholesale clients across June, July and August
    
    WITH table1 AS (
    SELECT 
      EXTRACT(month FROM date)::int AS month_int,	
      (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,
    	ROUND((SUM(total) * (1-payment_fee))::numeric,2) AS net_revenue
    FROM sales 
    WHERE client_type = 'Wholesale'
    GROUP BY month_int, month, payment_fee
    ORDER BY month DESC)
    
    SELECT 
      t1.month, 
      t1.net_revenue AS current_month_net_revenue, 
      t2.net_revenue AS previous_month_net_revenue,
      (t1.net_revenue - t2.net_revenue) AS diff,
      (CASE WHEN t1.net_revenue > t2.net_revenue THEN 'Increase'
        WHEN t1.net_revenue < t2.net_revenue THEN 'Decrease'
        WHEN t1.net_revenue = t2.net_revenue THEN 'Constant' END) AS trend
    FROM table1 AS t1
    LEFT JOIN table1 AS t2
    ON t1.month_int = t2.month_int + 1
    GROUP BY t1.month, t1.net_revenue, t2.net_revenue
    ORDER BY month DESC
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.