CUPSAnalytics

01 · Data model

How the tables connect

A star-like schema: orders is the central fact table, linked to customers, cafés and promo codes; each order has one or more order_items, each pointing to a product.

03 · SQL in practice

From query to insight

SELECT
    hour,
    COUNT(*) AS orders
FROM orders
GROUP BY hour
ORDER BY hour;

SELECT
    CASE
        WHEN cnt = 1              THEN '1 order'
        WHEN cnt BETWEEN 2 AND 5   THEN '2–5 orders'
        WHEN cnt BETWEEN 6 AND 15  THEN '6–15 orders'
        ELSE '16+ orders'
    END AS bucket,
    COUNT(*) AS customers
FROM (
    SELECT customer_id, COUNT(*) AS cnt
    FROM orders
    WHERE status = 'picked_up'
    GROUP BY customer_id
) sub
GROUP BY bucket;

WITH weekly AS (
    SELECT DATE_TRUNC('week', date)::DATE AS week, SUM(total) AS revenue
    FROM orders WHERE status = 'picked_up'
    GROUP BY DATE_TRUNC('week', date)
)
SELECT week, revenue,
       LAG(revenue) OVER (ORDER BY week) AS prev_week,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY week))
             / LAG(revenue) OVER (ORDER BY week), 1) AS wow_growth_pct
FROM weekly ORDER BY week;

CTE + LAG window function: compares each week with the previous one — the standard way to track growth.

05 · DAX & findings

Measures behind the dashboards

-- total sales
Revenue = SUM( order_items[line_total] )

-- average order value
Avg Check = AVERAGE( orders[total] )

-- share of cancelled orders
Cancel Rate =
DIVIDE(
    CALCULATE( COUNTROWS(orders),
        orders[status] = "cancelled" ),
    COUNTROWS(orders) )

Key findings