- ****************************************************************************
- ****************
- ************************
- ******> 8 ******
Table 1: walmart_brand
col_name | col_type --------------+------------------- brand_id | bigint brand_name | text product_id | bigint
Table 2: walmart_order
col_name | col_type --------------+------------------- order_id | bigint product_id | bigint order_dt | date qty | integer
Sample results
brand_category | count ----------------+------- High | 1 Low | 2 Medium | 2
Solution postgres
WITH brand_sales AS (
SELECT B.brand_id, COUNT(order_id) AS cnt
FROM walmart_order O
INNER JOIN walmart_brand B
ON B.product_id = O.product_id
WHERE order_dt >= '2021-08-01'
AND order_dt <= '2021-08-31'
GROUP BY B.brand_id
)
SELECT CASE WHEN cnt <=3 THEN 'Low'
WHEN cnt < 8 THEN 'Medium'
WHEN cnt >= 8 THEN 'High'
ELSE NULL END AS brand_category,
COUNT(*)
FROM brand_sales
GROUP BY 1;
Explanation
This query is designed to categorize brands based on the number of orders they received during the month of August 2021.
First, the query creates a Common Table Expression (CTE) called "brand_sales" that aggregates the number of orders for each brand. The CTE uses an inner join to match order data from the "walmart_order" table with product data from the "walmart_brand" table. The WHERE clause filters the results to only include orders placed in August 2021. The resulting table has two columns: "brand_id" and "cnt" (short for count).
Next, the main query uses a CASE statement to create three categories for brands based on their order count: "Low" for brands with 3 or fewer orders, "Medium" for brands with more than 3 but less than 8 orders, and "High" for brands with 8 or more orders. The query then counts the number of brands in each category and groups the results by the category using the GROUP BY clause.
Overall, this query provides a simple way to analyze brand performance based on order volume, which could be useful for identifying popular products or evaluating marketing strategies.
Expected results
More Walmart questions
| ID | Title | Level | FTPR |
|---|---|---|---|
| 162 | Number of orders per brand walmart | easy |
|
| 163 | Average number of orders per brand walmart | easy |
|
| 165 | Session stitching walmart | hard |
|
| 166 | Top 10 customers based on spend growth walmart | medium |
|