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
| Column | Data type | Description |
|---|---|---|
order_number | VARCHAR | Unique order number. |
date | DATE | Date of the order, from June to August 2021. |
warehouse | VARCHAR | The warehouse that the order was made from— North, Central, or West. |
client_type | VARCHAR | Whether the order was Retail or Wholesale. |
product_line | VARCHAR | Type of product ordered. |
quantity | INT | Number of products ordered. |
unit_price | FLOAT | Price per product (dollars). |
total | FLOAT | Total price of the order (dollars). |
payment | VARCHAR | Payment method—Credit card, Transfer, or Cash. |
payment_fee | FLOAT | Percentage of total charged as a result of the payment method. |
Your query output should be presented in the following format:
product_line | month | warehouse | net_revenue |
|---|---|---|---|
| product_one | --- | --- | --- |
| product_one | --- | --- | --- |
| product_one | --- | --- | --- |
| product_one | --- | --- | --- |
| product_one | --- | --- | --- |
| product_one | --- | --- | --- |
| product_two | --- | --- | --- |
| ... | ... | ... | ... |
--AQUÍ ME ESTÁN PIDIENDO SACAR EL NET REVENUE POR LINEA DE PRODUCTO, MES Y ALMACÉN --
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' AND EXTRACT('month' from date) BETWEEN 5 AND 8
GROUP BY product_line, warehouse, EXTRACT('month' from date)
ORDER BY product_line, EXTRACT('month' from date), net_revenue DESC
Extended Project below
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.
-- AQUÍ ME PIDEN SACAR EL NET REVENUE PERO AHORA POR TIPO DE MEDIO DE PAGO
WITH payments AS (
SELECT
warehouse,
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,
payment,
SUM(total) - SUM(payment_fee) AS net_revenue
FROM sales
WHERE EXTRACT('month' from date) BETWEEN 5 AND 8
GROUP BY warehouse, EXTRACT('month' from date), payment
ORDER BY warehouse, EXTRACT('month' from date), net_revenue DESC),
payments_rank AS (
SELECT
warehouse,
month,
payment,
RANK() OVER (PARTITION BY warehouse,month ORDER BY net_revenue DESC) as rank
FROM payments)
SELECT
*
FROM payments_rank
WHERE rank = 1
ORDER BY warehouse,month DESC
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.
-- AQUÍ ME PIDEN SACAR EL TOP 3 PRODUCTOS MÁS PEDIDOS POR CADA TIPO DE CLIENTE
WITH clients AS (
SELECT
client_type,
product_line,
COUNT(order_number) AS times_ordered
FROM sales
GROUP BY product_line, client_type
ORDER BY product_line, times_ordered DESC),
clients_line_rank as (
SELECT
client_type,
product_line,
RANK() OVER (PARTITION BY client_type ORDER BY times_ordered DESC) as rank
FROM clients
)
SELECT
*
FROM clients_line_rank
WHERE rank <= 3
ORDER BY client_type,rank ASC