End To End Case Study: Measurement Frameworks Questions

-- Dashboard logic
SELECT SUM(amount) AS dashboard_revenue FROM dashboard_table
WHERE revenue_month = '2025-10';

-- Ad-hoc logic
SELECT SUM(amount) AS adhoc_revenue FROM raw_transactions
WHERE transaction_ts >= '2025-10-01' AND transaction_ts < '2025-11-01';

SELECT t.*
FROM raw_transactions t
LEFT JOIN dashboard_table d ON t.tx_id = d.tx_id
WHERE t.transaction_ts >= '2025-10-01' AND t.transaction_ts < '2025-11-01'
  AND (d.tx_id IS NULL OR t.amount<>d.amount);

SELECT source, load_date, COUNT(*) AS cnt, SUM(amount) FROM staging GROUP BY source, load_date;

SELECT COUNT(*) FROM staging WHERE tx_id IS NULL OR amount IS NULL;
SELECT tx_id, COUNT(*) FROM staging GROUP BY tx_id HAVING COUNT(*)>1;

SELECT DATE(transaction_ts AT TIME ZONE 'UTC' AT TIME ZONE 'America/NY') AS day, SUM(amount)
FROM raw_transactions GROUP BY day ORDER BY day;

SELECT COUNT(*) FROM transformed WHERE date_trunc('month', transaction_ts AT TIME ZONE 'UTC') = '2025-10-01';

SELECT tx_id, source_file, etl_run_id FROM lineage WHERE tx_id IN ( ... );

SELECT week_start, region, wau
FROM metrics.waus
WHERE region = 'EMEA'
  AND week_start BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 6 WEEK) AND CURRENT_DATE
ORDER BY week_start;

SELECT week_start, dau, wau, mau
FROM metrics.user_activity
WHERE region='EMEA' ORDER BY week_start;

SELECT week_start, SUM(event_count) as events
FROM events.ingest_stats
WHERE region='EMEA' GROUP BY week_start ORDER BY week_start;

SELECT country, platform, SUM(active_users) AS wau
FROM metrics.waus_daily
WHERE week_start = '2025-11-10'
GROUP BY country, platform ORDER BY wau DESC LIMIT 20;