## Practice SQL questions on the shopping dataset.SQL Practice: ShopDB - Codeverra

Codeverra · SQL Practice

SQL Practice Set
ShopDB

12 questions on an Indian e-commerce dataset - customers, products, orders, and line items. Easy to hard. Every question includes the business context, a one-line approach, a clean query, and alternatives where they exist.

Dataset Schema · 5 tables

customers - 20 rows

id, name, email

city, state

joined_on DATE

categories - 6 rows

id, name

description nullable

products - 25 rows

id, name

category_id → categories

price_inr, stock_quantity

is_available BOOLEAN

orders - 15 rows

id, customer_id → customers

order_date DATE

status Delivered/Shipped/Pending/Cancelled

payment_method UPI/Card/COD/NetBanking

order_items - 30 rows

id, order_id → orders

product_id → products

quantity, unit_price

Easy - single/two table

Medium - multi-table JOINs, HAVING

Hard - CTEs, window functions, subqueries

Easy

Single or two tables · WHERE · basic aggregates · CASE WHEN

Easy

How many products are in each category?

GROUP BY · COUNT · JOIN

The product team wants a quick count of how many SKUs live in each category - Electronics, Books, Sports, etc. Show the category name, not the id.

Approach

JOIN products to categories to get the name, then GROUP BY category and COUNT(*). The JOIN is necessary because the name lives in a separate table - products only stores category_id.

SELECT
    c.name          AS category,
    COUNT(*)        AS product_count,
    ROUND(AVG(p.price_inr), 0) AS avg_price_inr
FROM   products    AS p
JOIN   categories  AS c  ON c.id = p.category_id
GROUP BY c.id, c.name
ORDER BY product_count DESC;

Always include the JOIN key (c.id) in GROUP BY alongside any display column - if two categories accidentally shared a name, GROUP BY c.name alone would merge them incorrectly.

Easy

Label every product as Budget, Mid-range, or Premium

CASE WHEN · SELECT

Marketing needs to segment the product catalogue by price band for a promotional email - budget items for one campaign, premium for another.

Approach

Use CASE WHEN to create a computed column. Conditions are checked top to bottom - first match wins, so order matters. No JOIN needed - everything lives on products.

SELECT
    name,
    price_inr,
    stock_quantity,
    CASE
        WHEN price_inr < 1000                      THEN 'Budget'
        WHEN price_inr BETWEEN 1000 AND 9999       THEN 'Mid-range'
        ELSE                                             'Premium'
    END                                              AS price_segment
FROM   products
ORDER BY price_inr;

Alternative - add a count summary

Wrap the above in a subquery (or CTE) and GROUP BY price_segment to get how many products fall into each band - useful as a dashboard metric.

BETWEEN 1000 AND 9999 is inclusive on both ends. A product priced exactly ₹1000 matches the Mid-range condition. CASE WHEN conditions short-circuit - once a row matches, remaining conditions are skipped.

Easy

Monthly order count for 2024 - how busy was each month?

EXTRACT · GROUP BY · ORDER BY

Operations wants to know which months in 2024 saw the highest order volume - to plan staffing and logistics for the next year.

Approach

Use EXTRACT(MONTH FROM order_date) to pull the month number out of the date, then GROUP BY that. Filter the year with WHERE EXTRACT(YEAR FROM order_date) = 2024.

SELECT
    EXTRACT(MONTH FROM order_date)  AS month,
    TO_CHAR(order_date, 'Month')    AS month_name,
    COUNT(*)                         AS orders_placed
FROM   orders
WHERE  EXTRACT(YEAR FROM order_date) = 2024
GROUP BY month, month_name
ORDER BY month;

TO_CHAR(order_date, 'Month') gives a readable month name alongside the number. PostgreSQL's EXTRACT returns a number (1–12) - you need it in GROUP BY to aggregate correctly. TO_CHAR is just for display.

Easy

Order status breakdown - delivered, pending, shipped, cancelled

GROUP BY · COUNT · FILTER pivot

The ops dashboard needs a single-row summary of all orders by status - how many delivered, how many still pending, how many cancelled.

Approach

Two ways: GROUP BY status gives one row per status (vertical), or use COUNT(*) FILTER (WHERE status = '...') to get all statuses in one horizontal row - the "pivot" pattern.

-- Vertical: one row per status
SELECT status, COUNT(*) AS count
FROM   orders
GROUP BY status
ORDER BY count DESC;

-- Horizontal pivot: everything in one row
SELECT
    COUNT(*)                                            AS total,
    COUNT(*) FILTER (WHERE status = 'Delivered')      AS delivered,
    COUNT(*) FILTER (WHERE status = 'Pending')        AS pending,
    COUNT(*) FILTER (WHERE status = 'Shipped')        AS shipped,
    COUNT(*) FILTER (WHERE status = 'Cancelled')      AS cancelled
FROM   orders;

The pivot form is useful when the result needs to feed a dashboard widget that expects one row. The GROUP BY form is more flexible - add a new status value and it appears automatically. FILTER is PostgreSQL-specific; use SUM(CASE WHEN status = 'X' THEN 1 ELSE 0 END) for portability.

Medium

Multi-table JOINs · revenue calculations · HAVING · anti-join

Medium

Total revenue by category - excluding cancelled orders

4-table JOIN · SUM · GROUP BY · WHERE

Finance needs a revenue breakdown by product category. Revenue = quantity × unit_price at the line item level. Cancelled orders must be excluded.

Approach

Chain four tables: categories → products → order_items → orders. Filter out cancelled orders in WHERE. Group and sum. Use COUNT(DISTINCT o.id) for order count - without DISTINCT, you'd count line items instead of orders.

SELECT
    cat.name                           AS category,
    SUM(oi.quantity * oi.unit_price)   AS revenue_inr,
    COUNT(DISTINCT o.id)               AS orders_count,
    SUM(oi.quantity)                   AS units_sold
FROM   categories   AS cat
JOIN   products     AS p   ON p.category_id  = cat.id
JOIN   order_items  AS oi  ON oi.product_id  = p.id
JOIN   orders       AS o   ON oi.order_id    = o.id
WHERE  o.status != 'Cancelled'
GROUP BY cat.id, cat.name
ORDER BY revenue_inr DESC;

Revenue is calculated at line item level (quantity × price per item), not order level. Electronics dominates because phones and laptops are high-value. COUNT(DISTINCT o.id) counts unique orders - without DISTINCT an order with 3 items would be counted 3 times.

Medium

Products that have never been ordered - dead stock alert

LEFT JOIN anti-join · IS NULL · NOT EXISTS · NOT IN

The inventory team wants to flag products that have sat in the catalogue with zero orders - candidates for discounting or delisting.

Approach

Three valid patterns all produce the same result: (1) LEFT JOIN + WHERE IS NULL (anti-join), (2) NOT EXISTS subquery, (3) NOT IN subquery. The LEFT JOIN pattern is the most commonly seen in production.

-- Method 1: LEFT JOIN anti-join (most common)
SELECT p.name, cat.name AS category, p.price_inr, p.stock_quantity
FROM   products    AS p
JOIN   categories  AS cat  ON cat.id = p.category_id
LEFT JOIN order_items AS oi  ON oi.product_id = p.id
WHERE  oi.id IS NULL
ORDER BY cat.name, p.name;

-- Method 2: NOT EXISTS (safest with NULLs)
SELECT p.name, p.price_inr
FROM   products AS p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi
    WHERE oi.product_id = p.id
);

-- Method 3: NOT IN (add NULL guard!)
SELECT name, price_inr
FROM   products
WHERE  id NOT IN (
    SELECT product_id FROM order_items
    WHERE  product_id IS NOT NULL  -- critical
);

Why NOT IN needs the NULL guard

If SELECT product_id FROM order_items returns even one NULL, the entire NOT IN returns zero rows - a silent bug. NOT EXISTS handles NULLs correctly by design and is generally the safer choice.

The LEFT JOIN approach works because a product with no order_items gets NULL on the right side of the join. WHERE oi.id IS NULL keeps only those unmatched rows. It is called an anti-join - finding rows in A that don't exist in B.

Medium

Top 5 customers by total spend

JOIN · GROUP BY · SUM · LIMIT

Marketing wants to identify the highest-value customers for a loyalty programme - top 5 by total spend on non-cancelled orders, with their city and state.

Approach

Join the full chain from customers through to order_items, filter out cancelled orders, group by customer, sum the revenue, and take the top 5 with ORDER BY + LIMIT.

SELECT
    c.name                             AS customer,
    c.city,
    c.state,
    COUNT(DISTINCT o.id)               AS orders_placed,
    SUM(oi.quantity * oi.unit_price)   AS total_spent_inr
FROM   customers    AS c
JOIN   orders       AS o   ON o.customer_id = c.id
JOIN   order_items  AS oi  ON oi.order_id   = o.id
WHERE  o.status != 'Cancelled'
GROUP BY c.id, c.name, c.city, c.state
ORDER BY total_spent_inr DESC
LIMIT 5;

Customers with no orders don't appear - INNER JOIN excludes them. If you want all customers including those with zero spend, switch to LEFT JOINs and use COALESCE(SUM(...), 0). The top customer's spend should be well above ₹40,000 - likely a phone or laptop order.

Medium

Customers who placed more than one order - repeat buyers

GROUP BY · HAVING · COUNT DISTINCT

The retention team wants to identify repeat buyers - customers who have placed at least 2 orders - as the foundation for a loyalty programme analysis.

Approach

Join customers → orders → order_items, group by customer, then use HAVING COUNT(DISTINCT o.id) > 1. The HAVING clause filters after grouping - you cannot use WHERE here because COUNT doesn't exist until after aggregation.

SELECT
    c.name                             AS customer,
    c.city,
    COUNT(DISTINCT o.id)               AS total_orders,
    SUM(oi.quantity * oi.unit_price)   AS total_spent_inr
FROM   customers    AS c
JOIN   orders       AS o   ON o.customer_id = c.id
JOIN   order_items  AS oi  ON oi.order_id   = o.id
WHERE  o.status != 'Cancelled'
GROUP BY c.id, c.name, c.city
HAVING  COUNT(DISTINCT o.id) > 1
ORDER BY total_orders DESC, total_spent_inr DESC;

Common mistake

Using COUNT(o.id) instead of COUNT(DISTINCT o.id) counts order items, not orders. A single order with 3 items would count as 3 - incorrectly showing the customer as a repeat buyer.

WHERE runs before grouping, HAVING runs after. This is part of SQL's execution order: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Any condition on an aggregate must go in HAVING.

Hard

CTEs · window functions · nested aggregation · full report queries

Hard

Best-selling product in each category

CTE · RANK() OVER PARTITION BY · window function

Merchandising wants the single best-selling product (by units sold) in each category - to feature it in a homepage banner. If two products tie, both should appear.

Approach

Two-step: first aggregate sales per product per category in a CTE, then apply RANK() OVER (PARTITION BY category ORDER BY units_sold DESC). Filter WHERE rnk = 1 in the outer query. RANK handles ties - ROW_NUMBER would arbitrarily pick one.

WITH product_sales AS (
    SELECT
        cat.id                             AS cat_id,
        cat.name                           AS category,
        p.name                             AS product,
        SUM(oi.quantity)                   AS units_sold,
        SUM(oi.quantity * oi.unit_price)   AS revenue_inr,
        RANK() OVER (
            PARTITION BY cat.id
            ORDER BY SUM(oi.quantity) DESC
        )                                  AS rnk
    FROM   categories   AS cat
    JOIN   products     AS p   ON p.category_id = cat.id
    JOIN   order_items  AS oi  ON oi.product_id = p.id
    JOIN   orders       AS o   ON oi.order_id   = o.id
    WHERE  o.status != 'Cancelled'
    GROUP BY cat.id, cat.name, p.id, p.name
)
SELECT category, product, units_sold, revenue_inr
FROM   product_sales
WHERE  rnk = 1
ORDER BY category;

You cannot filter on a window function in the same SELECT - WHERE RANK() OVER (...) = 1 is invalid. The CTE (or subquery) materialises the ranked result first, then the outer query can filter on rnk. This two-step pattern appears constantly in analytics work.

Hard

Monthly revenue with running cumulative total

DATE_TRUNC · GROUP BY · SUM OVER · running total

Finance wants a month-by-month revenue table with a running cumulative total - so they can see at a glance when the business crossed key revenue milestones across the year.

Approach

Group by month to get monthly revenue, then apply SUM(monthly_revenue) OVER (ORDER BY month) for the running total. The tricky part: you cannot directly nest SUM(SUM(...)) - use a CTE or SUM(SUM()) OVER which is valid in one pass.

SELECT
    EXTRACT(MONTH FROM o.order_date)             AS month,
    TO_CHAR(o.order_date, 'Mon YYYY')            AS month_label,
    SUM(oi.quantity * oi.unit_price)              AS monthly_revenue,
    SUM(SUM(oi.quantity * oi.unit_price))
        OVER (ORDER BY EXTRACT(MONTH FROM o.order_date))
                                                  AS running_total
FROM   orders       AS o
JOIN   order_items  AS oi  ON oi.order_id = o.id
WHERE  o.status != 'Cancelled'
GROUP BY month, month_label
ORDER BY month;

Why SUM(SUM(...)) OVER works

The inner SUM(oi.quantity * oi.unit_price) is evaluated per GROUP BY group first, producing one revenue number per month. The outer SUM(...) OVER (ORDER BY month) then accumulates those monthly totals in date order - a two-level aggregation in one query.

The final row's running_total equals the gross revenue across all non-cancelled orders. Verify it against SELECT SUM(quantity * unit_price) FROM order_items JOIN orders... WHERE status != 'Cancelled' - they should match exactly.

Hard

Average order value by payment method - using a CTE

CTE · nested aggregation · AVG · ROUND

The payments team wants to know if customers who pay by card tend to spend more per order than UPI or COD customers - average order value broken down by payment method.

Approach

You cannot write AVG(SUM(...)) directly - aggregate functions can't be nested. Use a CTE to compute each order's total first, then AVG those totals in the outer query. This is a very common two-step pattern.

WITH order_totals AS (
    SELECT
        o.id                               AS order_id,
        o.payment_method,
        SUM(oi.quantity * oi.unit_price)   AS order_total
    FROM   orders       AS o
    JOIN   order_items  AS oi  ON oi.order_id = o.id
    WHERE  o.status != 'Cancelled'
    GROUP BY o.id, o.payment_method
)
SELECT
    payment_method,
    COUNT(*)                           AS order_count,
    ROUND(AVG(order_total), 2)         AS avg_order_value_inr,
    ROUND(MIN(order_total), 2)         AS min_order,
    ROUND(MAX(order_total), 2)         AS max_order
FROM   order_totals
GROUP BY payment_method
ORDER BY avg_order_value_inr DESC;

The CTE order_totals runs as a named subquery - you can test it independently before adding the outer aggregation. This is the main reason CTEs exist: making complex two-step queries readable and debuggable one step at a time.

Hard

Full customer report - spend, tier, and order breakdown in one query

CTE · LEFT JOIN · FILTER · CASE WHEN · COALESCE · ROUND

The CEO wants one table: every customer, how many orders they placed, how many were delivered vs cancelled, their total spend, average order value, last order date, and a tier label (Platinum / Gold / Silver / No Orders).

Approach

Use a CTE with LEFT JOINs (so all 20 customers appear, even those with no orders). Aggregate everything in the CTE, then apply CASE WHEN for tier in the outer SELECT. Separate the aggregation step from the classification step - it makes both readable.

WITH customer_stats AS (
    SELECT
        c.id, c.name, c.city, c.state,
        COUNT(DISTINCT o.id)                           AS total_orders,
        COUNT(DISTINCT o.id) FILTER (WHERE o.status = 'Delivered')
                                                        AS delivered,
        COUNT(DISTINCT o.id) FILTER (WHERE o.status = 'Cancelled')
                                                        AS cancelled,
        COALESCE(
            SUM(oi.quantity * oi.unit_price)
                FILTER (WHERE o.status != 'Cancelled'),
            0
        )                                               AS total_spent,
        MAX(o.order_date)                               AS last_order_date
    FROM   customers    AS c
    LEFT JOIN orders     AS o   ON o.customer_id = c.id
    LEFT JOIN order_items AS oi  ON oi.order_id   = o.id
    GROUP BY c.id, c.name, c.city, c.state
)
SELECT
    name, city, state,
    total_orders, delivered, cancelled,
    total_spent                          AS total_spent_inr,
    CASE
        WHEN total_orders > 0
        THEN ROUND(total_spent / total_orders, 0)
        ELSE 0
    END                                  AS avg_order_value,
    last_order_date,
    CASE
        WHEN total_spent > 50000   THEN '💎 Platinum'
        WHEN total_spent >= 10000  THEN '🥇 Gold'
        WHEN total_spent > 0       THEN '🥈 Silver'
        ELSE                             ' -  No Orders'
    END                                  AS tier
FROM   customer_stats
ORDER BY total_spent DESC;

All 20 customers appear because of LEFT JOINs. COALESCE(..., 0) converts NULL revenue (customers with no orders) to 0. The division-by-zero guard in CASE WHEN is essential - without it, customers with zero orders would cause a divide-by-zero error when calculating average order value.

SQL Practice on Shopping Dataset: SQL Questions with Solutions

SQL Practice Set
ShopDB

What this practice set covers

SQL Practice SetShopDB

What this practice set covers

SQL Practice Set
ShopDB