SQL

• Select specific columns, filter with WHERE, and sort with ORDER BY
• Translate KPI definitions into column logic (CASE, COALESCE)

• Join fact tables (events, orders) to dimensions (customers, products)
• Understand keys, one-to-many joins, and how to avoid duplication

• Summaries with GROUP BY and HAVING
• Distinct counts, conditional sums, and ratio metrics

• Rankings, rolling averages, period-over-period comparisons
• Calendar tables, date truncation, and gap-filling

• Encapsulate KPI logic into views for dashboards
• Add comments, stable column names, and version-friendly patterns

• Filter early, aggregate before joining where possible
• Use explain plans (if available), limit scanned data, and validate results

-- Tables: customer_events(customer_id, event_date, event_type)
SELECT 
  event_date,
  COUNT(DISTINCT customer_id) AS daily_active_customers
FROM customer_events
WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY event_date
ORDER BY event_date;

Tip: Always specify the date window to keep queries fast and relevant.

-- Tables: orders(order_id, customer_id, order_date, total_amount)
--          customers(customer_id, segment)
SELECT 
  c.segment,
  DATE_TRUNC('month', o.order_date) AS month,
  SUM(o.total_amount) AS revenue
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.segment, DATE_TRUNC('month', o.order_date)
ORDER BY month, c.segment;

Why LEFT JOIN? You keep valid orders even if the customer dimension is missing a row.

-- Monthly product revenue with a minimum threshold for reporting
-- Tables: order_items(order_id, product_id, qty, unit_price), orders(order_id, order_date)
SELECT 
  DATE_TRUNC('month', o.order_date) AS month,
  oi.product_id,
  SUM(oi.qty * oi.unit_price) AS product_revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
GROUP BY month, oi.product_id
HAVING SUM(oi.qty * oi.unit_price) >= 1000
ORDER BY month, product_revenue DESC;

HAVING filters after aggregation. Use it for thresholds on aggregated metrics.

-- Table: daily_revenue(date_day, revenue)
SELECT 
  date_day,
  revenue,
  SUM(revenue) OVER (
    ORDER BY date_day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d_revenue,
  revenue - LAG(revenue) OVER (ORDER BY date_day) AS revenue_dod_change
FROM daily_revenue
ORDER BY date_day;

Window frames let you create smooth trends and comparisons without extra joins.

-- Prefer the latest non-null attribute per customer
-- Table: customer_attributes(customer_id, email, updated_at)
WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY updated_at DESC
         ) AS rn
  FROM customer_attributes
)
SELECT customer_id,
       COALESCE(email, 'unknown@example.com') AS email
FROM ranked
WHERE rn = 1;

ROW_NUMBER de‑duplicates; COALESCE gives safe defaults for downstream dashboards.

-- Create a view that BI tools can query directly
-- Tables: orders(order_id, order_date, customer_id, total_amount)
--         customers(customer_id, segment)
CREATE OR REPLACE VIEW v_monthly_segment_revenue AS
SELECT 
  DATE_TRUNC('month', o.order_date) AS month,
  c.segment,
  SUM(o.total_amount) AS revenue,
  COUNT(DISTINCT o.customer_id) AS unique_buyers
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
GROUP BY month, c.segment;

Keep view columns stable and documented. This reduces breakage across dashboards.

Symptom: metrics double. Fix: aggregate fact tables before joining, or join dimensions first then aggregate once.

WHERE filters rows before aggregation; HAVING filters after aggregation. Use HAVING to filter sums, counts, or averages.

Always normalize to a grain (e.g., DATE_TRUNC('month', ts)) before grouping or joining to date dimensions.

Default frames may differ by SQL dialect. Specify ROWS/RANGE frames explicitly for rolling metrics.

Use COALESCE around denominators and CASE for safe division. Example: CASE WHEN denom > 0 THEN num::decimal/denom END.

Always filter by date when possible to reduce the amount of data scanned.

• 3–4 CREATE VIEW statements with clear column names
• Sample queries that demonstrate the KPIs
• A short note on assumptions and edge cases (NULLs, late-arriving data)