-- Create SCD Type 2 dimension table
CREATE TABLE dim_customer (
  customer_key SERIAL PRIMARY KEY,
  customer_id INT,
  customer_name VARCHAR(255),
  email VARCHAR(255),
  city VARCHAR(100),
  effective_date DATE,
  expiry_date DATE,
  is_current BOOLEAN DEFAULT true
);

-- Insert new version on change (2-step process)
-- Step 1: Expire current record
UPDATE dim_customer
SET expiry_date = CURRENT_DATE - INTERVAL '1 day',
    is_current = false
WHERE customer_id = 101 AND is_current = true;

-- Step 2: Insert new version
INSERT INTO dim_customer (
  customer_id, customer_name, email, city,
  effective_date, expiry_date, is_current
)
VALUES (
  101, 'John Updated', 'john.new@email.com', 'New York',
  CURRENT_DATE, '9999-12-31', true
);

-- Find active record for a specific date
SELECT customer_id, customer_name, city
FROM dim_customer
WHERE customer_id = 101
  AND '2024-06-15'::DATE >= effective_date
  AND '2024-06-15'::DATE < expiry_date;

-- BigQuery SYSTEM_TIME temporal table
CREATE TABLE `project.dataset.orders` (
  order_id INT64, customer_id INT64,
  total_amount NUMERIC, status STRING,
  valid_from TIMESTAMP, valid_to TIMESTAMP
);

-- Query as of specific timestamp
SELECT *
FROM `project.dataset.orders`
FOR SYSTEM_TIME AS OF '2024-06-15 10:00:00'
WHERE order_id = 12345;

-- Query between two timestamps
SELECT *
FROM `project.dataset.orders`
FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-06-30'
WHERE customer_id = 101;

-- Fill missing dates in time series (PostgreSQL)
WITH date_range AS (
  SELECT generate_series(
    '2024-01-01'::DATE,
    '2024-12-31'::DATE,
    '1 day'::INTERVAL
  )::DATE AS sale_date
)
SELECT
  dr.sale_date,
  COALESCE(daily.total_sales, 0) AS total_sales,
  COALESCE(daily.order_count, 0) AS order_count
FROM date_range dr
LEFT JOIN (
  SELECT sale_date, SUM(amount) AS total_sales, COUNT(*) AS order_count
  FROM sales
  WHERE sale_date >= '2024-01-01' AND sale_date <= '2024-12-31'
  GROUP BY sale_date
) daily ON dr.sale_date = daily.sale_date
ORDER BY dr.sale_date;

-- Group events into sessions (30-min inactivity = new session)
WITH events_with_gap AS (
  SELECT
    user_id, event_time, event_type,
    LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event_time
  FROM events
),
session_assignments AS (
  SELECT *,
    SUM(CASE
      WHEN event_time - prev_event_time > INTERVAL '30 minutes'
        OR prev_event_time IS NULL
      THEN 1 ELSE 0
    END) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
  FROM events_with_gap
)
SELECT
  user_id, session_id,
  MIN(event_time) AS session_start,
  MAX(event_time) AS session_end,
  MAX(event_time) - MIN(event_time) AS session_duration,
  COUNT(*) AS event_count
FROM session_assignments
GROUP BY user_id, session_id;

-- Find gaps in event sequences
WITH numbered_events AS (
  SELECT
    event_id, event_type, event_time,
    LAG(event_time) OVER (PARTITION BY event_type ORDER BY event_time) AS prev_event_time
  FROM events
)
SELECT
  event_type,
  prev_event_time AS gap_start,
  event_time AS gap_end,
  event_time - prev_event_time AS gap_duration,
  CASE
    WHEN event_time - prev_event_time > INTERVAL '1 hour' THEN 'LARGE_GAP'
    ELSE 'NORMAL'
  END AS gap_type
FROM numbered_events
WHERE prev_event_time IS NOT NULL
  AND event_time - prev_event_time > INTERVAL '30 minutes';

-- Join two temporal tables for point-in-time analysis
SELECT
  o.order_id, o.order_date,
  c.customer_name, c.city,
  p.product_name, p.category
FROM orders o
INNER JOIN dim_customer c
  ON o.customer_id = c.customer_id
  AND o.order_date >= c.effective_date
  AND o.order_date < c.expiry_date
INNER JOIN dim_product p
  ON o.product_id = p.product_id
  AND o.order_date >= p.effective_date
  AND o.order_date < p.expiry_date;

-- Calculate month-over-month growth
WITH monthly_sales AS (
  SELECT
    DATE_TRUNC('month', sale_date)::DATE AS month,
    SUM(amount) AS total_sales
  FROM sales
  GROUP BY 1
)
SELECT
  month, total_sales,
  LAG(total_sales) OVER (ORDER BY month) AS prev_month,
  total_sales - LAG(total_sales) OVER (ORDER BY month) AS absolute_growth,
  ROUND(
    (total_sales - LAG(total_sales) OVER (ORDER BY month)) * 100.0 /
    NULLIF(LAG(total_sales) OVER (ORDER BY month), 0), 2
  ) AS growth_pct
FROM monthly_sales
ORDER BY month;