Structured Query Language Fundamentals and Aggregation Questions

-- 1. Hourly aggregates (job runs each hour)
INSERT INTO hourly_aggregates (event_day, event_hour, cnt, source_hour)
SELECT
  date_trunc('day', event_time)      AS event_day,
  date_trunc('hour', event_time)     AS event_hour,
  count(*)                           AS cnt,
  date_trunc('hour', now()) - interval '1 hour' AS source_hour -- watermark
FROM raw_events
WHERE event_time >= date_trunc('hour', now()) - interval '2 hour'  -- process recent window
GROUP BY 1,2;

-- 2. Merge hourly aggregates into daily aggregates (runs after hourly_aggregates populated)
MERGE INTO daily_aggregates d
USING (
  SELECT event_day, sum(cnt) AS day_cnt
  FROM hourly_aggregates
  WHERE source_hour = date_trunc('hour', now()) - interval '1 hour'
  GROUP BY event_day
) h
ON d.event_day = h.event_day
WHEN MATCHED THEN
  UPDATE SET cnt = d.cnt + h.day_cnt, updated_at = now()
WHEN NOT MATCHED THEN
  INSERT (event_day, cnt, updated_at) VALUES (h.event_day, h.day_cnt, now());

WITH cleaned AS (
  SELECT
    tx_id,
    tx_ts,
    -- remove anything except digits, dot, and leading minus
    NULLIF(
      regexp_replace(coalesce(raw_amount, ''), '^[\s"\']+|[\s"\']+$', '', 'g') -- trim quotes/spaces
      , ''
    ) AS raw_trimmed
  FROM staging
),
normalized AS (
  SELECT
    tx_id,
    tx_ts,
    -- remove $ and commas and any currency whitespace, keep leading -
    regexp_replace(raw_trimmed, '[$,]', '', 'g') AS raw_no_commas
  FROM cleaned
),
parsed AS (
  SELECT
    tx_id,
    tx_ts,
    raw_no_commas,
    CASE
      WHEN raw_no_commas IS NULL OR raw_no_commas = '' THEN NULL
      WHEN raw_no_commas ~ '^[-+]?\d+(\.\d+)?$' THEN (raw_no_commas)::numeric
      ELSE NULL -- malformed: could also log/store for inspection
    END AS amount
  FROM normalized
)
SELECT
  date(tx_ts) AS tx_date,
  count(*) FILTER (WHERE amount IS NOT NULL) AS n_transactions,
  sum(amount) AS total_revenue,
  count(*) FILTER (WHERE amount IS NULL) AS n_bad_rows
FROM parsed
GROUP BY tx_date
ORDER BY tx_date;

SELECT
  country,
  AVG(order_amount)        AS aov,
  COUNT(DISTINCT user_id)  AS unique_customers,
  COUNT(*)                 AS orders_count
FROM orders
GROUP BY country
HAVING
  AVG(order_amount) > 100
  AND COUNT(DISTINCT user_id) >= 100;

CREATE TABLE t(a INT);
INSERT INTO t VALUES (1), (NULL), (2), (NULL);
SELECT COUNT(a) AS count_col, COUNT(*) AS count_star FROM t;
-- count_col = 2  (NULLs ignored)
-- count_star = 4 (rows counted)

CREATE TABLE s(g INT, v INT);
INSERT INTO s VALUES (1,10), (1,NULL);
SELECT g, SUM(v) AS sum_v FROM s GROUP BY g HAVING g=2;
-- No row returned for g=2 (no group). If you SELECT a group that exists but all v NULL:
SELECT g, SUM(v) FROM s WHERE g=1 GROUP BY g;
-- sum_v = 10  (NULL ignored). If all v are NULL SUM returns NULL, not 0.

INSERT INTO s VALUES (NULL,5), (NULL,NULL);
SELECT g, COUNT(*) FROM s GROUP BY g;
-- one group with g = NULL, count = 2

SELECT
  user_id,
  COUNT(DISTINCT session_id) AS session_count
FROM events
WHERE event_ts BETWEEN '2025-01-01' AND '2025-01-31'  -- replace with your range
  AND session_id IS NOT NULL
GROUP BY user_id;

SELECT
  user_id,
  COUNT(DISTINCT COALESCE(session_id, CONCAT('__NULL__', event_id))) AS session_count
FROM events
WHERE event_ts BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY user_id;