Modern Data Infrastructure
for ABC Foodmart

Track category-level revenue across months to detect demand shifts, seasonal dips, and growth windows replacing the manual monthly spreadsheet previously emailed between stores.

SELECT
    cat.category_name,
    EXTRACT(MONTH FROM s.sales_date) AS month,
    SUM(s.total_price)             AS monthly_revenue
FROM sales s
JOIN products  p   ON s.product_id  = p.product_id
JOIN categories cat ON p.category_id = cat.category_id
GROUP BY cat.category_name, month
ORDER BY cat.category_name, month;
SQL

Sample output Beverages category, first 5 months:

Identify which categories have strong vs. weak seasonal patterns. A seasonality index above 1.0 indicates a peak month; below 1.0 indicates a trough guiding procurement timing and promotional calendars.

WITH monthly_rev AS (
    SELECT
        cat.category_name,
        EXTRACT(MONTH FROM s.sales_date) AS month,
        SUM(s.total_price) AS revenue
    FROM sales s
    JOIN products p    ON s.product_id  = p.product_id
    JOIN categories cat ON p.category_id = cat.category_id
    GROUP BY cat.category_name, month
),
category_avg AS (
    SELECT category_name, AVG(revenue) AS avg_revenue
    FROM monthly_rev
    GROUP BY category_name
)
SELECT
    m.category_name,
    m.month,
    m.revenue,
    ROUND(m.revenue / c.avg_revenue, 2) AS seasonality_index
FROM monthly_rev m
JOIN category_avg c ON m.category_name = c.category_name
ORDER BY m.category_name, m.month;
SQL · CTE

Surface the single best-performing month for each category using a window function replacing the manual chart-reading previously done in Excel.

WITH monthly AS (
    SELECT
        cat.category_name,
        EXTRACT(MONTH FROM s.sales_date) AS month,
        SUM(s.total_price) AS revenue
    FROM sales s
    JOIN products p    ON s.product_id  = p.product_id
    JOIN categories cat ON p.category_id = cat.category_id
    GROUP BY cat.category_name, month
)
SELECT category_name, month, revenue
FROM (
    SELECT
        category_name, month, revenue,
        RANK() OVER (
            PARTITION BY category_name
            ORDER BY revenue DESC
        ) AS rnk
    FROM monthly
) ranked
WHERE rnk = 1
ORDER BY category_name;
SQL · RANK() window fn

Identify "hero products" driving category revenue (for sourcing and shelf priority) and the weakest performers (for rationalization or repositioning). The same CTE is reused only the ORDER BY direction changes.

WITH product_revenue AS (
    SELECT
        cat.category_name,
        p.product_id,
        p.product_name,
        SUM(s.total_price) AS revenue
    FROM sales s
    JOIN products p    ON s.product_id  = p.product_id
    JOIN categories cat ON p.category_id = cat.category_id
    GROUP BY cat.category_name, p.product_id, p.product_name
),
ranked AS (
    SELECT *,
        RANK() OVER (
            PARTITION BY category_name
            ORDER BY revenue DESC  -- swap ASC for lowest
        ) AS rnk
    FROM product_revenue
)
SELECT category_name, product_id, product_name,
       revenue AS total_revenue
FROM ranked
WHERE rnk = 1
ORDER BY category_name;
SQL · RANK() PARTITION BY

Measure discount elasticity which products actually sell more when discounted, and which don't respond. Prevents margin loss from blanket promotions on inelastic items.

WITH product_qty AS (
    SELECT
        p.product_id,
        p.product_name,
        CASE WHEN s.discount > 0
             THEN 'discounted'
             ELSE 'no_discount'
        END AS price_type,
        AVG(s.quantity) AS avg_qty
    FROM sales s
    JOIN products p ON s.product_id = p.product_id
    GROUP BY p.product_id, p.product_name, price_type
),
pivoted AS (
    SELECT
        product_id, product_name,
        MAX(CASE WHEN price_type = 'discounted'
               THEN avg_qty END) AS avg_qty_discount,
        MAX(CASE WHEN price_type = 'no_discount'
               THEN avg_qty END) AS avg_qty_no_discount
    FROM product_qty
    GROUP BY product_id, product_name
)
SELECT
    product_id, product_name,
    avg_qty_discount,
    avg_qty_no_discount,
    (avg_qty_discount - avg_qty_no_discount)
        AS qty_increase_due_to_discount
FROM pivoted
ORDER BY qty_increase_due_to_discount DESC NULLS LAST
LIMIT 10;
SQL · CASE pivot

Create a dynamic revenue leaderboard for all salespeople supporting performance reviews, bonus calculations, and territory planning without any manual Excel sorting.

SELECT
    emp.employee_id,
    emp.first_name || ' ' || emp.last_name AS employee_name,
    SUM(s.total_price) AS revenue,
    RANK() OVER (
        ORDER BY SUM(s.total_price) DESC
    ) AS revenue_rank
FROM sales s
JOIN employees emp ON emp.employee_id = s.sales_person_id
GROUP BY emp.employee_id, employee_name
ORDER BY revenue DESC;
SQL · RANK() global

Rank salespeople within their local market accounting for the fact that a city with lower overall demand should be measured differently. Identifies regional champions whose practices can be replicated.

WITH city_employee_revenue AS (
    SELECT
        city.city_name,
        emp.employee_id,
        emp.first_name || ' ' || emp.last_name AS employee_name,
        SUM(s.total_price) AS revenue
    FROM sales s
    JOIN employees emp  ON s.sales_person_id = emp.employee_id
    JOIN customers cust ON s.customer_id     = cust.customer_id
    JOIN cities city    ON cust.city_id      = city.city_id
    GROUP BY city.city_name, emp.employee_id, employee_name
),
ranked AS (
    SELECT *,
        RANK() OVER (
            PARTITION BY city_name
            ORDER BY revenue DESC
        ) AS rnk
    FROM city_employee_revenue
)
SELECT city_name, employee_id, employee_name,
       revenue AS total_revenue
FROM ranked
WHERE rnk = 1
ORDER BY city_name;
SQL · RANK() PARTITION BY city

Reveal which countries and cities drive the most sales informing store expansion planning, marketing resource allocation, and inventory logistics.

SELECT
    ctry.country_name,
    city.city_name,
    SUM(s.total_price) AS total_revenue
FROM sales s
JOIN customers cust ON s.customer_id  = cust.customer_id
JOIN cities city    ON cust.city_id   = city.city_id
JOIN countries ctry ON city.country_id = ctry.country_id
GROUP BY ctry.country_name, city.city_name
ORDER BY total_revenue DESC;
SQL · 4-table JOIN

Local preferences vary stocking Confections uniformly across all cities ignores meaningful regional differences. This query enables geographic assortment tailoring.

WITH city_category_revenue AS (
    SELECT
        city.city_name,
        cat.category_name,
        SUM(s.total_price) AS revenue
    FROM sales s
    JOIN customers cust ON s.customer_id  = cust.customer_id
    JOIN cities city    ON cust.city_id   = city.city_id
    JOIN products p     ON s.product_id   = p.product_id
    JOIN categories cat ON p.category_id  = cat.category_id
    GROUP BY city.city_name, cat.category_name
),
ranked AS (
    SELECT *,
        RANK() OVER (
            PARTITION BY city_name
            ORDER BY revenue DESC
        ) AS rnk
    FROM city_category_revenue
)
SELECT
    city_name,
    category_name AS top_category,
    revenue       AS total_revenue
FROM ranked
WHERE rnk = 1
ORDER BY city_name;
SQL · 5-table JOIN + RANK() PARTITION BY