SQL Scenarios Questions

CREATE INDEX idx_users_lower_email ON users (lower(email));

CREATE INDEX idx_orders_active ON orders (customer_id) WHERE status = 'active';

CREATE INDEX idx_metrics_user_time ON metrics (user_id, event_date) INCLUDE (value, event_type);

WITH params AS (
  SELECT current_date AS today, current_date - interval '365 days' AS start_date
),
dates AS (
  SELECT generate_series(start_date::date, today::date, interval '1 day')::date AS d
  FROM params
),
products AS (
  SELECT DISTINCT product_id FROM sales
),
calendar AS (
  SELECT p.product_id, d.d
  FROM products p CROSS JOIN dates d
),
daily AS (
  -- daily totals including zeros
  SELECT c.product_id, c.d AS sale_date,
         COALESCE(sum(s.amount), 0)::numeric AS amount
  FROM calendar c
  LEFT JOIN sales s
    ON s.product_id = c.product_id AND s.sale_date = c.d
  GROUP BY c.product_id, c.d
),
pct AS (
  -- day-over-day percent change; NULL when previous amount = 0 to avoid infinite
  SELECT
    product_id,
    sale_date,
    amount,
    lag(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS prev_amount,
    CASE
      WHEN lag(amount) OVER (PARTITION BY product_id ORDER BY sale_date) IS NULL THEN NULL
      WHEN lag(amount) OVER (PARTITION BY product_id ORDER BY sale_date) = 0 THEN NULL
      ELSE (amount - lag(amount) OVER (PARTITION BY product_id ORDER BY sale_date)) / NULLIF(lag(amount) OVER (PARTITION BY product_id ORDER BY sale_date),0)
    END AS pct_change
  FROM daily
),
window_max AS (
  -- max single-day percent change inside any 7-day window ending at each day
  SELECT
    product_id,
    sale_date,
    pct_change,
    max(pct_change) OVER (
      PARTITION BY product_id
      ORDER BY sale_date
      RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
    ) AS max_in_7d
  FROM pct
)
SELECT product_id,
       MAX(max_in_7d) AS best_single_day_pct_growth
FROM window_max
GROUP BY product_id
ORDER BY best_single_day_pct_growth DESC NULLS LAST
LIMIT 3;

BEGIN;

-- 1. identify changes (including back-dated)
WITH latest AS (
  SELECT *
  FROM dim_customer
  WHERE is_current = true
),
changes AS (
  SELECT s.*
  FROM staged_customers s
  LEFT JOIN latest d ON s.customer_id = d.customer_id
  WHERE d.customer_id IS NULL -- new customer
     OR (s.name IS DISTINCT FROM d.name OR s.address IS DISTINCT FROM d.address)
)

-- 2. close/expire overlapping rows in dim_customer for changed customers
UPDATE dim_customer dc
SET effective_to = (c.change_ts::date - INTERVAL '1 day')::date,
    is_current = false
FROM changes c
WHERE dc.customer_id = c.customer_id
  AND (dc.is_current = true OR dc.effective_from <= c.change_ts::date AND (dc.effective_to IS NULL OR dc.effective_to >= c.change_ts::date));

-- 3. insert new versions (one per staged row)
INSERT INTO dim_customer (customer_sk, customer_id, name, address, effective_from, effective_to, is_current)
SELECT nextval('dim_customer_sk_seq'), s.customer_id, s.name, s.address, s.change_ts::date, NULL, true
FROM changes s;

COMMIT;