SQL Dialects and Built-in Functions Questions

Approach: use jsonb_array_elements to expand the items array, defensively extract item_id and price using ->> and try_cast (or safe numeric cast) and filter out null/invalid values before aggregating.

-- Flatten payload.items, extract item_id and numeric price, sum revenue per item_id
WITH items AS (
  SELECT
    e.id AS event_id,
    elem ->> 'item_id' AS item_id_text,
    -- extract price as text then cast to numeric safely
    NULLIF(elem ->> 'price', '') AS price_text
  FROM events e
  CROSS JOIN LATERAL
    jsonb_array_elements(COALESCE(e.payload -> 'items', '[]'::jsonb)) AS elem
)
, parsed AS (
  SELECT
    event_id,
    item_id_text,
    -- only keep rows with non-null item_id
    CASE WHEN item_id_text IS NOT NULL THEN item_id_text END AS item_id,
    -- try to cast price_text to numeric; invalid casts become NULL
    CASE
      WHEN price_text ~ '^[0-9]+(\.[0-9]+)?$' THEN (price_text)::numeric
      ELSE NULL
    END AS price
  FROM items
)
SELECT
  item_id,
  SUM(price) AS total_revenue,
  COUNT(*) FILTER (WHERE price IS NOT NULL) AS price_count,
  COUNT(*) AS rows_seen
FROM parsed
WHERE item_id IS NOT NULL
GROUP BY item_id
ORDER BY total_revenue DESC;

Key points:

• jsonb_array_elements expands arrays into rows.
• Use COALESCE to handle missing items key.
• Extract with ->> to get text, validate/cast price to numeric (regex guarded cast).
• Filter null item_id or invalid prices before aggregation.

Edge cases:

• Missing payload or items → handled by COALESCE.
• price as number vs string → casting from text covers both if validated.
• Non-numeric price or malformed JSON → filtered out (NULL) to avoid errors.

Alternatives:

• Use jsonb_to_recordset to define schema if items uniform.
• Use TRY_CAST extension or plpgsql for complex coercion logic.

Difference 1 — NULL-safe equality (handling NULLs in join keys)
Problem: Some dialects treat NULL != NULL; Postgres has IS NOT DISTINCT FROM, MySQL has the NULL-safe <=> operator, Snowflake/BigQuery lack these exact operators.

Portable pattern (works everywhere): explicit NULL-aware comparison
SELECT t1., t2.
FROM t1
JOIN t2
ON (t1.key = t2.key OR (t1.key IS NULL AND t2.key IS NULL));

Notes per DB:

• Postgres: you can instead write ON t1.key IS NOT DISTINCT FROM t2.key (more concise).
• MySQL: you can use ON t1.key <=> t2.key.
• BigQuery / Snowflake: use the portable OR pattern above (or COALESCE with sentinel only if you can guarantee sentinel not present).

Why: explicit boolean condition is standard SQL and avoids dialect-specific operators.

Difference 2 — LATERAL / APPLY (joining to a subquery or table-valued function per row)
Problem: Postgres uses LATERAL, SQL Server uses CROSS/OUTER APPLY, Snowflake supports LATERAL, BigQuery doesn't have APPLY but supports UNNEST/ARRAY subqueries.

Goal: get most recent order per user (top-1 per user) via per-row subquery.

Portable approach using window functions (recommended when portability matters):
SELECT user_id, order_id, order_ts
FROM (
SELECT u.user_id, o.order_id, o.order_ts,
ROW_NUMBER() OVER (PARTITION BY u.user_id ORDER BY o.order_ts DESC) rn
FROM users u
JOIN orders o ON o.user_id = u.user_id
) t
WHERE rn = 1;

Dialect-specific lateral form (when you need a table function per row and window is not an option):

• Postgres:
  FROM users u
  LEFT JOIN LATERAL (
  SELECT * FROM orders o WHERE o.user_id = u.user_id ORDER BY order_ts DESC LIMIT 1
  ) o ON true;
• SQL Server:
  OUTER APPLY (
  SELECT TOP (1) * FROM orders o WHERE o.user_id = u.user_id ORDER BY order_ts DESC
  ) o
• BigQuery:
  LEFT JOIN (
  SELECT AS STRUCT * FROM UNNEST(
  ARRAY(
  SELECT AS STRUCT o.* FROM orders o WHERE o.user_id = users.user_id ORDER BY order_ts DESC LIMIT 1
  )
  )
  ) o ON TRUE
• Snowflake:
  LEFT JOIN LATERAL (
  SELECT * FROM orders o WHERE o.user_id = users.user_id ORDER BY order_ts DESC LIMIT 1
  ) o ON TRUE;

Recommendation: prefer window-function solution for portability and performance. Use lateral/apply only when invoking table-valued functions or when LIMIT per-group is more natural and your target DB supports it.

Approach: convert each UTC timestamptz to America/Los_Angeles local time, truncate to month in that timezone, filter by the local-year, then aggregate. This ensures month boundaries respect local DST shifts.

-- Replace :year with the desired year (e.g., 2024)
WITH orders_local AS (
  SELECT
    date_trunc('month', order_date AT TIME ZONE 'America/Los_Angeles')::date AS month_start,
    total_amount
  FROM orders
  WHERE (order_date AT TIME ZONE 'America/Los_Angeles') >= make_timestamp(:year, 1, 1, 0, 0, 0)
    AND (order_date AT TIME ZONE 'America/Los_Angeles') <  make_timestamp(:year + 1, 1, 1, 0, 0, 0)
)
SELECT
  to_char(month_start, 'YYYY-MM') AS month,
  SUM(total_amount) AS revenue
FROM orders_local
GROUP BY month_start
ORDER BY month_start;

Key points:

• order_date AT TIME ZONE 'America/Los_Angeles' converts UTC timestamptz into local timestamp, so date_trunc('month', ...) aligns to local month boundaries (handles DST).
• Filtering is done on the localized timestamp to include only orders whose local date falls in the requested year.
• To include zero-revenue months, left-join a generate_series of monthly month_start dates for that year.

Approach: use Postgres INSERT ... ON CONFLICT for an idempotent upsert that only updates when incoming row is newer (prevents rewinding updated_at) and is stable if retried. Show ANSI MERGE equivalent. Then discuss performance and patterns (staging, batching, COPY, dedupe, contention, indexing).

Postgres idempotent upsert:

-- incoming predictions in a staging table or values; using values for example
INSERT INTO model_predictions(user_id, prediction, updated_at)
VALUES
  (123, 0.78, '2025-11-20T10:00:00Z'),
  (124, 0.12, '2025-11-20T10:01:00Z')
ON CONFLICT (user_id) DO UPDATE
SET
  prediction = EXCLUDED.prediction,
  updated_at = EXCLUDED.updated_at
-- idempotency guard: only apply if incoming is newer
WHERE model_predictions.updated_at < EXCLUDED.updated_at;

ANSI SQL MERGE (Postgres 15+ supports MERGE; generic ANSI form):

MERGE INTO model_predictions AS tgt
USING (VALUES (123,0.78,'2025-11-20T10:00:00Z')) AS src(user_id,prediction,updated_at)
ON tgt.user_id = src.user_id
WHEN MATCHED AND tgt.updated_at < src.updated_at THEN
  UPDATE SET prediction = src.prediction, updated_at = src.updated_at
WHEN NOT MATCHED THEN
  INSERT (user_id,prediction,updated_at) VALUES (src.user_id,src.prediction,src.updated_at);

Performance implications & patterns:

• Frequent single-row upserts cause high WAL, index contention, and bloat. Hot rows lead to lock contention.
• Prefer batching: collect predictions into a staging table (temporary or partitioned), load with COPY (fast, minimal WAL with unlogged/temporary), dedupe by user_id keeping max(updated_at).
• Then perform a single set-based MERGE/INSERT ... SELECT ... ON CONFLICT from staging. This reduces transactions and indexes churn.
• Use partitioning and smaller indexes for scale; monitor VACUUM and autovacuum tuning.
• For extreme throughput, consider append-only storage + periodic compaction, or use an external low-latency store (Redis) for real-time, and batch flush to Postgres.
• Ensure idempotency via updated_at or version token; include metrics and retry logic in pipeline.

Approach: expand (unnest) the JSON array into rows, extract a canonical tag identifier from each tag object, deduplicate per event before joining to tags_master so repeated tag entries within an event don’t produce duplicate joins.

Postgres (jsonb) — use a lateral join and DISTINCT ON to dedupe per event_id + tag_id:

WITH exploded AS (
  SELECT
    e.event_id,
    elem ->> 'tag_id' AS tag_id
  FROM events e
  CROSS JOIN LATERAL jsonb_array_elements(e.attributes) AS arr(elem)
)
, unique_event_tags AS (
  -- dedupe tag occurrences per event
  SELECT DISTINCT event_id, tag_id
  FROM exploded
  WHERE tag_id IS NOT NULL
)
SELECT
  u.event_id,
  u.tag_id,
  tm.tag_name,
  tm.category
FROM unique_event_tags u
LEFT JOIN tags_master tm
  ON u.tag_id = tm.tag_id;

BigQuery (JSON stored as STRING or JSON) — use UNNEST and DISTINCT:

WITH exploded AS (
  SELECT
    event_id,
    JSON_VALUE(tag, '$.tag_id') AS tag_id
  FROM `project.dataset.events`,
  UNNEST(JSON_EXTRACT_ARRAY(attributes)) AS tag
)
SELECT
  e.event_id,
  e.tag_id,
  tm.tag_name,
  tm.category
FROM (
  SELECT DISTINCT event_id, tag_id
  FROM exploded
  WHERE tag_id IS NOT NULL
) e
LEFT JOIN `project.dataset.tags_master` tm
  ON e.tag_id = tm.tag_id;

Key points:

• Extract a stable tag identifier (tag_id) rather than whole object to dedupe reliably.
• Deduplicate (SELECT DISTINCT or GROUP BY) before joining to avoid multiplicative duplicates.
• Handle NULLs and malformed JSON with WHERE tag_id IS NOT NULL or try/SAFE_ JSON functions.
• If tag matching is fuzzy (name vs id), normalize (lower(), trim()) before dedupe/join.

Complexity: O(N) in number of tag entries; cost dominated by JSON parsing and join to tags_master (index on tags_master.tag_id recommended).