ufo-ai/ufo-core/data-sql-queries

SQL Queries

Dialect-specific reference and common analytical patterns for data warehouse SQL.

Dialect Quick Reference

Date Arithmetic

Case-Insensitive Matching

JSON / Semi-Structured Access

Performance Tips by Dialect

• PostgreSQL: EXPLAIN ANALYZE, EXISTS over IN for subqueries, partial indexes
• Snowflake: Clustering keys (not indexes), filter on cluster columns, RESULT_SCAN(LAST_QUERY_ID()) to reuse results
• BigQuery: Always filter on partition column, APPROX_COUNT_DISTINCT() for cardinality, avoid SELECT * (billed per byte)
• Redshift: DISTKEY for collocated joins, SORTKEY for filters, watch for DS_BCAST/DS_DIST in plan
• Databricks: OPTIMIZE + ZORDER, CACHE TABLE, partition by low-cardinality date columns

Window Functions

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)
SUM(revenue) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total
AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d
LAG(value, 1) OVER (PARTITION BY entity ORDER BY date) as prev_value
revenue / SUM(revenue) OVER (PARTITION BY category) as pct_of_category

Common Analytical Patterns

Deduplication:

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY updated_at DESC) as rn
    FROM source_table
)
SELECT * FROM ranked WHERE rn = 1;

Cohort retention:

SELECT
    c.cohort_month,
    COUNT(DISTINCT c.user_id) as cohort_size,
    COUNT(DISTINCT CASE WHEN a.month = c.cohort_month + INTERVAL '1 month' THEN a.user_id END) as month_1
FROM cohorts c LEFT JOIN activity a ON c.user_id = a.user_id
GROUP BY c.cohort_month;

Funnel conversion:

SELECT
    SUM(CASE WHEN event = 'view' THEN 1 END) as viewed,
    SUM(CASE WHEN event = 'signup' THEN 1 END) as signed_up,
    ROUND(100.0 * SUM(CASE WHEN event = 'signup' THEN 1 END)
        / NULLIF(SUM(CASE WHEN event = 'view' THEN 1 END), 0), 1) as conv_pct
FROM events WHERE event_date >= CURRENT_DATE - INTERVAL '30 days';

Gotchas

• BigQuery DATE_TRUNC argument order is reversed — It's DATE_TRUNC(col, MONTH) not DATE_TRUNC('month', col). This silently produces wrong results in some cases instead of erroring.
• LAST_VALUE needs explicit frame — Without ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, LAST_VALUE uses the default frame that ends at the current row, giving you the current row's value back.
• COUNT(DISTINCT) in window functions — Not supported in most dialects. Use a subquery or CTE to pre-aggregate instead.
• Integer division truncates silently — 5 / 2 = 2 in most dialects. Cast to float first: 5.0 / 2 or CAST(x AS FLOAT) / y.
• NULL poisons comparisons — WHERE col != 'x' excludes NULLs. Use WHERE col IS DISTINCT FROM 'x' (Postgres) or WHERE COALESCE(col, '') != 'x' if you want NULLs included.