Analyzing Motorcycle Sales

Beta

You're working for a company that sells motorcycle parts, and they've asked with 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 card, cash, and bank transfer as payment methods. However, each payment type incurs a different fee.

The board of directors want 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, 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:

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	---	---	---
...	...	...	...

Unknown integration

DataFrameavailable as

df

variable

WITH
salesCTE AS (
    SELECT  DISTINCT product_line,
    		TO_CHAR(date, 'Month') AS month,
    		warehouse,
    		SUM(total * (1 - payment_fee)) OVER (PARTITION BY product_line, TO_CHAR(date, 'Month'), warehouse) AS net_revenue,
    		client_type
FROM sales
)

SELECT product_line,
	   month,
       warehouse,
       ROUND(net_revenue::numeric, 2) AS net_revenue
FROM salesCTE
WHERE client_type = 'Wholesale'
ORDER BY product_line,month,net_revenue DESC;

This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.