Question 94
Instructions:
sales = sum(unit_price_usd * qty) .Hint
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
Solution
postgresWITH 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.
Last Submission
postgresNo submission yet for this engine. Run and submit your query to save it here.
Submit a query to compare against expected output.
Interview timer
Recommended interview pacing
Easy: 5 min for direct warm-up style questions.
Medium: 10 min for multi-step interview queries.
Hard: 15 min for layered questions with tighter time pressure.
A common bar is solving about 2 medium-or-harder questions in a 30 minute interview.
10:00
Run your query to preview results here.