164. Orders distribution by brand

easy walmart

  • ****************************************************************************
    • ****************
    • ************************
    • ******> 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
                      WHEN cnt < 8 THEN 'Medium'
                      WHEN cnt >= 8 THEN 'High'
                      ELSE NULL END AS brand_category,
FROM brand_sales


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