94. Top 3 products vs. bottom 3 products

medium amazon

Instructions:

  • Write a query to return the top 3 and bottom 3 products in August 2021 ranked by sales.
  • sales = sum(unit_price_usd * qty) .
  • ordering of your results is not considered

Hint

  • Make sure you clarify with the interviewer on how to deal with ties

Table: orders

An eCommerce company's online order table.

  col_name    | col_type
--------------+-------------------
order_id      | bigint
product_id    | bigint
customer_id   | bigint
order_dt      | date
qty           | integer
unit_price_usd| float
channel       | varchar(20) -- mobile, desktop

Sample results

 product_id | category
------------+----------
   10000045 | top
   10000060 | top
   10000067 | top
   10000089 | bottom
   10000036 | bottom
   10000065 | bottom

Expected results

Solution postgres

WITH top AS(
	SELECT product_id, SUM(unit_price_usd * qty)
	FROM orders
        WHERE order_dt >= '2021-08-01'
        AND order_dt < '2021-09-01'
	GROUP BY product_id
	ORDER BY SUM(unit_price_usd * qty) DESC 
	LIMIT 3
), 
bottom AS (
	SELECT product_id, SUM(unit_price_usd * qty) 
	FROM orders
        WHERE order_dt >= '2021-08-01'
        AND order_dt < '2021-09-01'
	GROUP BY product_id
	ORDER BY SUM(unit_price_usd * qty) 
	LIMIT 3
)
SELECT top.product_id, 'top'  AS category
FROM top
UNION ALL
SELECT bottom.product_id, 'bottom' AS category
FROM bottom;
    

Explanation

This query is used to identify the top and bottom selling products based on their revenue generated during a specific time period. The time period in question is between August 1, 2021, and September 1, 2021.

The query first creates two temporary tables using common table expressions (CTEs) named "top" and "bottom". These tables contain the top 3 and bottom 3 products, respectively, based on their revenue generated during the specified time period.

The revenue generated for each product is calculated by multiplying the unit price of the product with the quantity sold. This calculation is done for each order within the specified time period.

The "top" and "bottom" tables are then combined using a UNION ALL operator to create a final result set that contains the product IDs and a category column that identifies whether the product is in the top or bottom category.

Overall, this query helps a data analyst to quickly identify the products that are performing well or poorly during a specific time period, which can be useful for making business decisions such as inventory management or product promotions.


More Amazon questions

ID Title Level FTPR
93 number of sales for each product this month amazon easy
20%
95 Candidate products for subscription amazon hard
13%
108 Free premium membership amazon hard
16%
109 Top 3 most popular product categories amazon easy
11%
110 Most popular product by category amazon medium
12%
111 Second most popular product amazon medium
16%
112 Top 3 customers amazon easy
30%
113 Daily cumulative spend amazon easy
11%
114 Dod revenue change rate amazon easy
15%
115 Rolling average revenue amazon hard
28%
116 Top answers day by device amazon easy
14%
117 Rolling 7 days total answers amazon easy
35%
175 Employees' email account amazon easy
14%
176 Employees' annual bonus amazon easy
12%
177 Purchases by platform report amazon medium
10%