資料工程用 SQL

-- PostgreSQL
CREATE TABLE users (
  user_id      INT PRIMARY KEY,
  signup_date  DATE NOT NULL,
  country      TEXT
);

CREATE TABLE orders (
  order_id     INT PRIMARY KEY,
  user_id      INT NOT NULL REFERENCES users (user_id),
  order_date   DATE NOT NULL,
  amount       NUMERIC(10, 2) NOT NULL,
  status       TEXT NOT NULL            -- 'paid', 'refunded', 'cancelled'
);

CREATE TABLE events (
  user_id      INT NOT NULL,
  event_ts     TIMESTAMP NOT NULL,      -- UTC
  event_name   TEXT NOT NULL            -- 'view', 'cart', 'purchase'
);

CREATE TABLE employees (
  emp_id       INT PRIMARY KEY,
  name         TEXT NOT NULL,
  dept         TEXT NOT NULL,
  salary       NUMERIC(10, 2) NOT NULL,
  manager_id   INT REFERENCES employees (emp_id)   -- NULL for the CEO
);

1  FROM / JOIN / ON     build the row set
2  WHERE                filter rows            (no aggregates, no window functions)
3  GROUP BY             form groups
4  HAVING               filter groups          (aggregates allowed)
5  window functions     computed over the remaining rows
6  QUALIFY              filter on window results   (Snowflake, BigQuery, Databricks, DuckDB)
7  SELECT               compute output columns and aliases
8  DISTINCT             remove duplicate output rows
9  ORDER BY             sort                   (aliases allowed)
10 LIMIT / OFFSET       cut

-- PostgreSQL: users whose paid revenue in 2026 is above 1,000
SELECT user_id, SUM(amount) AS revenue
FROM   orders
WHERE  status = 'paid'                       -- row filter: cheap, before grouping
  AND  order_date >= DATE '2026-01-01'
GROUP  BY user_id
HAVING SUM(amount) > 1000;                   -- group filter

-- PostgreSQL: every user with their paid order count, including users with none
SELECT u.user_id, COUNT(o.order_id) AS paid_orders
FROM   users u
LEFT JOIN orders o
       ON o.user_id = u.user_id
      AND o.status  = 'paid'          -- in ON: keeps users with no paid orders
GROUP  BY u.user_id;
-- WHERE o.status = 'paid' here would drop every user without a paid order

-- PostgreSQL: users who placed at least one order (semi-join, no duplicates)
SELECT u.*
FROM   users u
WHERE  EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);

-- users who never ordered (anti-join) — three equivalent forms
SELECT u.* FROM users u
WHERE  NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);

SELECT u.* FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id
WHERE  o.user_id IS NULL;

SELECT user_id FROM users
EXCEPT
SELECT user_id FROM orders;

-- PostgreSQL: self-join — each employee next to their manager
SELECT e.name AS employee, m.name AS manager
FROM   employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id;

-- employees paid more than their manager
SELECT e.name, e.salary, m.salary AS manager_salary
FROM   employees e
JOIN   employees m ON m.emp_id = e.manager_id
WHERE  e.salary > m.salary;

-- PostgreSQL: non-equi (range) join — tag each order with the price tier it falls in
WITH tiers (tier, low, high) AS (
  VALUES ('small', 0, 50), ('medium', 50, 200), ('large', 200, 1000000)
)
SELECT o.order_id, o.amount, t.tier
FROM   orders o
JOIN   tiers t ON o.amount >= t.low AND o.amount < t.high;

-- PostgreSQL — WRONG: each order is repeated once per event of the same user
SELECT u.user_id, SUM(o.amount) AS revenue, COUNT(e.event_ts) AS events
FROM   users u
JOIN   orders o ON o.user_id = u.user_id
JOIN   events e ON e.user_id = u.user_id
GROUP  BY u.user_id;

-- RIGHT: aggregate each side to the user grain first, then join one row to one row
WITH rev AS (SELECT user_id, SUM(amount) AS revenue FROM orders GROUP BY user_id),
     ev  AS (SELECT user_id, COUNT(*)    AS events  FROM events GROUP BY user_id)
SELECT u.user_id, COALESCE(r.revenue, 0) AS revenue, COALESCE(v.events, 0) AS events
FROM   users u
LEFT JOIN rev r ON r.user_id = u.user_id
LEFT JOIN ev  v ON v.user_id = u.user_id;

-- PostgreSQL
SELECT 1 WHERE 3 NOT IN (1, 2);          -- one row
SELECT 1 WHERE 3 NOT IN (1, 2, NULL);    -- zero rows: 3 <> NULL is UNKNOWN

-- safe anti-join: NOT EXISTS ignores NULLs in the subquery
SELECT e.name
FROM   employees e
WHERE  NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.emp_id);  -- non-managers
-- NOT IN (SELECT manager_id FROM employees) would return nothing: the CEO's manager_id is NULL

-- PostgreSQL: per-country order metrics in a single scan
SELECT u.country,
       COUNT(*)                                          AS orders,
       COUNT(*)    FILTER (WHERE o.status = 'refunded')  AS refunds,
       SUM(o.amount) FILTER (WHERE o.status = 'paid')    AS paid_revenue,
       -- portable spelling of the same thing (BigQuery, Snowflake, Spark):
       SUM(CASE WHEN o.status = 'paid' THEN o.amount END) AS paid_revenue_case,
       ROUND(AVG(CASE WHEN o.status = 'refunded' THEN 1.0 ELSE 0 END), 3) AS refund_rate
FROM   orders o
JOIN   users  u ON u.user_id = o.user_id
GROUP  BY u.country;

-- PostgreSQL: revenue by country and month, with per-country subtotals and a grand total
SELECT u.country,
       DATE_TRUNC('month', o.order_date)::date AS month,
       SUM(o.amount)                          AS revenue,
       GROUPING(u.country, DATE_TRUNC('month', o.order_date)) AS grouping_level
FROM   orders o
JOIN   users  u ON u.user_id = o.user_id
GROUP  BY ROLLUP (u.country, DATE_TRUNC('month', o.order_date))
ORDER  BY u.country NULLS LAST, month NULLS LAST;

function(args) OVER (
    PARTITION BY  user_id                -- which rows are related   (optional: default = all rows)
    ORDER BY      order_date             -- order inside the partition
    ROWS BETWEEN  2 PRECEDING            -- the frame: which of those rows feed this row
              AND CURRENT ROW
)

salary   ROW_NUMBER   RANK   DENSE_RANK
 900         1          1        1
 800         2          2        2
 800         3          2        2
 700         4          4        3

-- PostgreSQL: days since each user's previous order, and the change in amount
SELECT user_id, order_date, amount,
       order_date - LAG(order_date) OVER w       AS days_since_prev,   -- DATE - DATE = INT
       amount     - LAG(amount, 1, 0) OVER w     AS diff_vs_prev,      -- default 0 for the first
       LEAD(order_date) OVER w                   AS next_order_date
FROM   orders
WINDOW w AS (PARTITION BY user_id ORDER BY order_date, order_id);

-- PostgreSQL: three different "last 7" windows on daily revenue
WITH daily AS (
  SELECT order_date, SUM(amount) AS revenue
  FROM   orders GROUP BY order_date
)
SELECT order_date, revenue,
       SUM(revenue) OVER (ORDER BY order_date
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)   AS running_total,
       AVG(revenue) OVER (ORDER BY order_date
                          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)           AS avg_last_7_rows,
       AVG(revenue) OVER (ORDER BY order_date
                          RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) AS avg_last_7_days,
       LAST_VALUE(revenue) OVER (ORDER BY order_date
                          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_day_revenue
FROM   daily;

-- PostgreSQL (any engine): the highest-paid employee in each department
WITH ranked AS (
  SELECT e.*, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, emp_id) AS rn
  FROM   employees e
)
SELECT emp_id, name, dept, salary FROM ranked WHERE rn = 1;

-- Snowflake (also BigQuery, Databricks, DuckDB): the same with QUALIFY
SELECT emp_id, name, dept, salary
FROM   employees
QUALIFY ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, emp_id) = 1;

-- PostgreSQL: employees earning above their department's average
-- correlated form
SELECT e.name, e.dept, e.salary
FROM   employees e
WHERE  e.salary > (SELECT AVG(x.salary) FROM employees x WHERE x.dept = e.dept);

-- window form: one pass, no self-reference
SELECT name, dept, salary
FROM  (SELECT e.*, AVG(salary) OVER (PARTITION BY dept) AS dept_avg FROM employees e) t
WHERE  salary > dept_avg;

-- PostgreSQL: the full reporting chain under the CEO, with depth and path
WITH RECURSIVE org AS (
  SELECT emp_id, name, manager_id, 0 AS depth, name::text AS path
  FROM   employees
  WHERE  manager_id IS NULL                              -- anchor: the root
  UNION ALL
  SELECT e.emp_id, e.name, e.manager_id, o.depth + 1, o.path || ' > ' || e.name
  FROM   employees e
  JOIN   org o ON e.manager_id = o.emp_id                -- recursive step: children
)
SELECT * FROM org ORDER BY path;

-- PostgreSQL: top 2 users by paid revenue in each country, ties included
WITH user_rev AS (
  SELECT u.country, o.user_id, SUM(o.amount) AS revenue
  FROM   orders o
  JOIN   users  u ON u.user_id = o.user_id
  WHERE  o.status = 'paid'
  GROUP  BY u.country, o.user_id
), ranked AS (
  SELECT *, DENSE_RANK() OVER (PARTITION BY country ORDER BY revenue DESC) AS rnk
  FROM   user_rev
)
SELECT country, user_id, revenue, rnk
FROM   ranked
WHERE  rnk <= 2
ORDER  BY country, rnk;

-- PostgreSQL: second-highest distinct salary, NULL if there is none
SELECT (SELECT DISTINCT salary
        FROM   employees
        ORDER  BY salary DESC
        OFFSET 1 LIMIT 1) AS second_highest_salary;

-- PostgreSQL: a change log that received order 1 twice
CREATE TEMP TABLE order_changes (order_id INT, status TEXT, updated_at TIMESTAMP);
INSERT INTO order_changes VALUES
  (1, 'paid',     TIMESTAMP '2026-11-01 10:00'),
  (1, 'refunded', TIMESTAMP '2026-11-03 09:00'),
  (2, 'paid',     TIMESTAMP '2026-11-02 12:00');

-- latest status per order
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn
  FROM   order_changes
)
SELECT order_id, status, updated_at FROM ranked WHERE rn = 1;

-- just find the duplicated keys
SELECT order_id, COUNT(*) FROM order_changes GROUP BY order_id HAVING COUNT(*) > 1;

-- PostgreSQL: per-user cumulative spend and a 3-order moving average
SELECT user_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date, order_id
                         ROWS UNBOUNDED PRECEDING)                   AS cumulative_spend,
       ROUND(AVG(amount) OVER (PARTITION BY user_id ORDER BY order_date, order_id
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3,
       ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY user_id), 1) AS pct_of_user_total
FROM   orders
WHERE  status = 'paid';

-- PostgreSQL: MoM and YoY revenue growth on a gap-free month spine
WITH months AS (
  SELECT generate_series(DATE '2025-01-01', DATE '2026-12-01', INTERVAL '1 month')::date AS month
), monthly AS (
  SELECT DATE_TRUNC('month', order_date)::date AS month, SUM(amount) AS revenue
  FROM   orders
  WHERE  status = 'paid'
  GROUP  BY 1
), filled AS (
  SELECT m.month, COALESCE(r.revenue, 0) AS revenue
  FROM   months m
  LEFT JOIN monthly r ON r.month = m.month
)
SELECT month, revenue,
       LAG(revenue)     OVER (ORDER BY month)                     AS prev_month,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1)  AS mom_pct,
       ROUND(100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY month))
             / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0), 1) AS yoy_pct
FROM   filled
ORDER  BY month;

login_date   row_number   login_date - row_number
2026-11-01       1            2026-10-31    ┐ island A
2026-11-02       2            2026-10-31    │
2026-11-03       3            2026-10-31    ┘
2026-11-05       4            2026-11-01    ┐ island B
2026-11-06       5            2026-11-01    ┘

-- PostgreSQL: users with a login streak of at least 3 days
WITH days AS (
  SELECT DISTINCT user_id, event_ts::date AS d          -- DISTINCT: one row per day
  FROM   events
), grp AS (
  SELECT user_id, d,
         d - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY d) AS INT) AS island
  FROM   days
)
SELECT user_id, MIN(d) AS streak_start, MAX(d) AS streak_end, COUNT(*) AS streak_days
FROM   grp
GROUP  BY user_id, island
HAVING COUNT(*) >= 3
ORDER  BY user_id, streak_start;

-- PostgreSQL: repeat purchases within 7 days of the user's previous order
SELECT user_id, order_date, prev_date, order_date - prev_date AS gap_days
FROM  (SELECT user_id, order_date,
              LAG(order_date) OVER (PARTITION BY user_id ORDER BY order_date, order_id) AS prev_date
       FROM   orders) t
WHERE  order_date - prev_date <= 7;

-- three strictly increasing order amounts in a row
SELECT DISTINCT user_id
FROM  (SELECT user_id, amount,
              LAG(amount, 1) OVER w AS prev1,
              LAG(amount, 2) OVER w AS prev2
       FROM   orders
       WINDOW w AS (PARTITION BY user_id ORDER BY order_date, order_id)) t
WHERE  prev2 < prev1 AND prev1 < amount;

-- PostgreSQL: 30-minute inactivity sessions
WITH flagged AS (
  SELECT user_id, event_ts, event_name,
         CASE WHEN event_ts - LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts)
                   <= INTERVAL '30 minutes'
              THEN 0 ELSE 1 END AS is_new_session            -- first event: LAG is NULL -> 1
  FROM   events
), numbered AS (
  SELECT *, SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_ts
                                      ROWS UNBOUNDED PRECEDING) AS session_no
  FROM   flagged
)
SELECT user_id, session_no,
       MIN(event_ts) AS session_start,
       MAX(event_ts) AS session_end,
       COUNT(*)      AS events
FROM   numbered
GROUP  BY user_id, session_no
ORDER  BY user_id, session_no;

-- PostgreSQL: monthly retention by signup cohort
WITH cohort AS (
  SELECT user_id, DATE_TRUNC('month', signup_date)::date AS cohort_month
  FROM   users
), activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', order_date)::date AS active_month
  FROM   orders
), joined AS (
  SELECT c.cohort_month, a.user_id,
         (EXTRACT(YEAR  FROM a.active_month) - EXTRACT(YEAR  FROM c.cohort_month)) * 12
       + (EXTRACT(MONTH FROM a.active_month) - EXTRACT(MONTH FROM c.cohort_month)) AS month_n
  FROM   cohort c
  JOIN   activity a ON a.user_id = c.user_id
), sizes AS (
  SELECT cohort_month, COUNT(*) AS cohort_size FROM cohort GROUP BY cohort_month
)
SELECT j.cohort_month, j.month_n, s.cohort_size,
       COUNT(DISTINCT j.user_id) AS active_users,
       ROUND(100.0 * COUNT(DISTINCT j.user_id) / s.cohort_size, 1) AS retention_pct
FROM   joined j
JOIN   sizes  s ON s.cohort_month = j.cohort_month
WHERE  j.month_n >= 0
GROUP  BY j.cohort_month, j.month_n, s.cohort_size
ORDER  BY j.cohort_month, j.month_n;

-- PostgreSQL: view -> cart -> purchase funnel
WITH steps AS (
  SELECT user_id,
         MIN(event_ts) FILTER (WHERE event_name = 'view')     AS t_view,
         MIN(event_ts) FILTER (WHERE event_name = 'cart')     AS t_cart,
         MIN(event_ts) FILTER (WHERE event_name = 'purchase') AS t_purchase
  FROM   events
  GROUP  BY user_id
)
SELECT COUNT(t_view)                                                AS viewed,
       COUNT(*) FILTER (WHERE t_cart >= t_view)                     AS carted,
       COUNT(*) FILTER (WHERE t_purchase >= t_cart
                          AND t_cart >= t_view)                     AS purchased
FROM   steps;

-- PostgreSQL: median and p90 order amount per country
SELECT u.country,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY o.amount) AS median_amount,
       PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY o.amount) AS median_actual_value,
       PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY o.amount) AS p90_amount
FROM   orders o
JOIN   users  u ON u.user_id = o.user_id
GROUP  BY u.country;

-- PostgreSQL: pivot — one row per user, one column per status
SELECT user_id,
       COUNT(*) FILTER (WHERE status = 'paid')      AS paid,
       COUNT(*) FILTER (WHERE status = 'refunded')  AS refunded,
       COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM   orders
GROUP  BY user_id;

-- unpivot — columns back into (metric, value) rows
WITH wide (user_id, paid, refunded) AS (VALUES (1, 3, 1), (2, 5, 0))
SELECT w.user_id, v.metric, v.value
FROM   wide w
CROSS JOIN LATERAL (VALUES ('paid', w.paid), ('refunded', w.refunded)) AS v (metric, value);

-- PostgreSQL: how many users placed 1, 2, 3 ... orders
SELECT order_count, COUNT(*) AS users
FROM  (SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id) per_user
GROUP  BY order_count
ORDER  BY order_count;

-- order amounts in 50-wide buckets
SELECT FLOOR(amount / 50) * 50 AS bucket_start, COUNT(*) AS orders
FROM   orders
GROUP  BY 1
ORDER  BY 1;

-- PostgreSQL: users who added to cart but never purchased
SELECT user_id
FROM   events
GROUP  BY user_id
HAVING COUNT(*) FILTER (WHERE event_name = 'cart')     > 0
   AND COUNT(*) FILTER (WHERE event_name = 'purchase') = 0;

-- customers who ordered in October 2026 but not in November 2026
SELECT DISTINCT o.user_id
FROM   orders o
WHERE  o.order_date >= DATE '2026-10-01' AND o.order_date < DATE '2026-11-01'
  AND  NOT EXISTS (SELECT 1 FROM orders n
                   WHERE  n.user_id = o.user_id
                     AND  n.order_date >= DATE '2026-11-01' AND n.order_date < DATE '2026-12-01');

-- PostgreSQL: rebuild each user's country history from a change log
CREATE TEMP TABLE user_changes (
  user_id     INT,
  country     TEXT,
  valid_from  DATE,       -- when the change took effect (event time)
  arrived_at  DATE,       -- when the pipeline received it
  source_seq  INT         -- the source's own order, e.g. an LSN
);
INSERT INTO user_changes VALUES
  (1, 'TW', DATE '2026-01-01', DATE '2026-01-01', 101),
  (1, 'KR', DATE '2026-03-01', DATE '2026-03-01', 103),
  (1, 'JP', DATE '2026-02-01', DATE '2026-03-05', 102),   -- arrived late
  (2, 'US', DATE '2026-01-15', DATE '2026-01-15', 201);

SELECT user_id, country,
       valid_from,
       COALESCE(LEAD(valid_from) OVER w, DATE '9999-12-31') AS valid_to,   -- exclusive
       LEAD(valid_from) OVER w IS NULL                       AS is_current
FROM   user_changes
WINDOW w AS (PARTITION BY user_id ORDER BY valid_from, source_seq)
ORDER  BY user_id, valid_from;

user_id  country  valid_from  valid_to    is_current
1        TW       2026-01-01  2026-02-01  false
1        JP       2026-02-01  2026-03-01  false
1        KR       2026-03-01  9999-12-31  true
2        US       2026-01-15  9999-12-31  true

-- PostgreSQL: more than one current version per key
SELECT user_id FROM dim_user_history WHERE is_current GROUP BY user_id HAVING COUNT(*) > 1;

-- overlapping versions of the same key: a version that starts before an earlier one ends
SELECT user_id, valid_from, valid_to, prev_max_to
FROM  (SELECT *,
              MAX(valid_to) OVER (PARTITION BY user_id ORDER BY valid_from, valid_to
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_max_to
       FROM   dim_user_history) t
WHERE  valid_from < prev_max_to;

-- PostgreSQL: the peak number of concurrent sessions, and when it happened
CREATE TEMP TABLE sessions (session_id INT, start_ts TIMESTAMP, end_ts TIMESTAMP);
INSERT INTO sessions VALUES
  (1, TIMESTAMP '2026-11-01 10:00', TIMESTAMP '2026-11-01 10:30'),
  (2, TIMESTAMP '2026-11-01 10:10', TIMESTAMP '2026-11-01 10:20'),
  (3, TIMESTAMP '2026-11-01 10:15', TIMESTAMP '2026-11-01 10:45'),
  (4, TIMESTAMP '2026-11-01 10:30', TIMESTAMP '2026-11-01 11:00');

WITH points AS (
  SELECT start_ts AS ts, +1 AS delta FROM sessions
  UNION ALL
  SELECT end_ts   AS ts, -1 AS delta FROM sessions
), running AS (
  SELECT ts,
         SUM(delta) OVER (ORDER BY ts, delta          -- -1 before +1 at the same instant
                          ROWS UNBOUNDED PRECEDING) AS open_sessions
  FROM   points
)
SELECT ts, open_sessions
FROM   running
ORDER  BY open_sessions DESC, ts
LIMIT  1;
-- 2026-11-01 10:15, 3 (sessions 1, 2 and 3)

-- PostgreSQL: one row per check, so the result can be stored and alerted on
SELECT 'null_order_id' AS check_name, COUNT(*) AS failures
FROM   orders WHERE order_id IS NULL
UNION ALL
SELECT 'duplicate_order_id', COUNT(*)
FROM  (SELECT order_id FROM orders GROUP BY order_id HAVING COUNT(*) > 1) d
UNION ALL
SELECT 'negative_amount', COUNT(*)
FROM   orders WHERE amount < 0
UNION ALL
SELECT 'unknown_status', COUNT(*)
FROM   orders WHERE status NOT IN ('paid', 'refunded', 'cancelled')
UNION ALL
SELECT 'orphan_user_id', COUNT(*)                              -- referential integrity
FROM   orders o WHERE NOT EXISTS (SELECT 1 FROM users u WHERE u.user_id = o.user_id);

-- PostgreSQL: row count and revenue per day, source vs target
WITH src AS (
  SELECT order_date, COUNT(*) AS n, SUM(amount) AS revenue
  FROM   staging_orders
  WHERE  order_date >= DATE '2026-11-01' AND order_date < DATE '2026-11-08'
  GROUP  BY order_date
), tgt AS (
  SELECT order_date, COUNT(*) AS n, SUM(amount) AS revenue
  FROM   orders
  WHERE  order_date >= DATE '2026-11-01' AND order_date < DATE '2026-11-08'
  GROUP  BY order_date
)
SELECT COALESCE(s.order_date, t.order_date) AS order_date,
       s.n AS src_rows, t.n AS tgt_rows, s.revenue AS src_revenue, t.revenue AS tgt_revenue
FROM   src s
FULL OUTER JOIN tgt t ON t.order_date = s.order_date      -- a day missing on either side shows up
WHERE  s.n IS DISTINCT FROM t.n
   OR  s.revenue IS DISTINCT FROM t.revenue;

-- PostgreSQL 15+ (also Snowflake, BigQuery, Spark SQL on Delta / Iceberg)
MERGE INTO dim_user AS t
USING (
  SELECT user_id, country, updated_at
  FROM  (SELECT s.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
         FROM   stg_user s) d
  WHERE  rn = 1                                    -- one source row per key
) AS s
ON t.user_id = s.user_id
WHEN MATCHED AND t.country IS DISTINCT FROM s.country THEN
  UPDATE SET country = s.country, updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (user_id, country, updated_at) VALUES (s.user_id, s.country, s.updated_at);

-- PostgreSQL 15+: CDC that may arrive late or be replayed; source_seq is the source's
-- order for the key (a log sequence number, a per-key version)
MERGE INTO dim_user AS t
USING (
  SELECT user_id, country, updated_at, source_seq
  FROM  (SELECT s.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY source_seq DESC) AS rn
         FROM   stg_user s) d
  WHERE  rn = 1                                    -- the newest change per key in this batch
) AS s
ON t.user_id = s.user_id
WHEN MATCHED AND s.source_seq > t.source_seq THEN  -- only a newer change wins
  UPDATE SET country = s.country, updated_at = s.updated_at, source_seq = s.source_seq
WHEN NOT MATCHED THEN
  INSERT (user_id, country, updated_at, source_seq)
  VALUES (s.user_id, s.country, s.updated_at, s.source_seq);

-- PostgreSQL: upsert a daily aggregate — rerunning the day overwrites it
INSERT INTO daily_revenue (day, revenue)
SELECT order_date, SUM(amount) FROM orders WHERE order_date = DATE '2026-11-01' GROUP BY order_date
ON CONFLICT (day) DO UPDATE SET revenue = EXCLUDED.revenue;

-- PostgreSQL: the Taipei business date of a UTC instant
SELECT (TIMESTAMPTZ '2026-11-01 18:30:00+00' AT TIME ZONE 'Asia/Taipei')::date AS taipei_date;
-- 2026-11-02: half past six in the evening UTC is already the next day in Taipei

-- PostgreSQL: daily active users for November, including days with zero activity
WITH spine AS (
  SELECT generate_series(DATE '2026-11-01', DATE '2026-11-30', INTERVAL '1 day')::date AS day
), dau AS (
  SELECT event_ts::date AS day, COUNT(DISTINCT user_id) AS dau
  FROM   events
  WHERE  event_ts >= TIMESTAMP '2026-11-01'
    AND  event_ts <  TIMESTAMP '2026-12-01'      -- half-open: no 23:59:59.999 edge
  GROUP  BY 1
)
SELECT s.day, COALESCE(d.dau, 0) AS dau
FROM   spine s
LEFT JOIN dau d ON d.day = s.day
ORDER  BY s.day;

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.country, SUM(o.amount)
FROM   orders o
JOIN   users  u ON u.user_id = o.user_id
WHERE  o.order_date >= DATE '2026-10-01'
GROUP  BY u.country;