Sql Queries

-- Current date/timeCURRENT_DATE, CURRENT_TIMESTAMP, NOW()
-- Date arithmeticdate_column + INTERVAL '7 days'date_column - INTERVAL '1 month'
-- Truncate to periodDATE_TRUNC('month', created_at)
-- Extract partsEXTRACT(YEAR FROM created_at)EXTRACT(DOW FROM created_at)  -- 0=Sunday
-- FormatTO_CHAR(created_at, 'YYYY-MM-DD')

-- Concatenationfirst_name || ' ' || last_nameCONCAT(first_name, ' ', last_name)
-- Pattern matchingcolumn ILIKE '%pattern%'  -- case-insensitivecolumn ~ '^regex_pattern$'  -- regex
-- String manipulationLEFT(str, n), RIGHT(str, n)SPLIT_PART(str, delimiter, position)REGEXP_REPLACE(str, pattern, replacement)

-- JSON accessdata->>'key'  -- textdata->'nested'->'key'  -- jsondata#>>'{path,to,key}'  -- nested text
-- Array operationsARRAY_AGG(column)ANY(array_column)array_column @> ARRAY['value']

-- Current date/timeCURRENT_DATE(), CURRENT_TIMESTAMP(), SYSDATE()
-- Date arithmeticDATEADD(day, 7, date_column)DATEDIFF(day, start_date, end_date)
-- Truncate to periodDATE_TRUNC('month', created_at)
-- Extract partsYEAR(created_at), MONTH(created_at), DAY(created_at)DAYOFWEEK(created_at)
-- FormatTO_CHAR(created_at, 'YYYY-MM-DD')

-- Case-insensitive by default (depends on collation)column ILIKE '%pattern%'REGEXP_LIKE(column, 'pattern')
-- Parse JSONcolumn:key::string  -- dot notation for VARIANTPARSE_JSON('{"key": "value"}')GET_PATH(variant_col, 'path.to.key')
-- Flatten arrays/objectsSELECT f.value FROM table, LATERAL FLATTEN(input => array_col) f

-- VARIANT type accessdata:customer:name::STRINGdata:items[0]:price::NUMBER
-- Flatten nested structuresSELECT    t.id,    item.value:name::STRING as item_name,    item.value:qty::NUMBER as quantityFROM my_table t,LATERAL FLATTEN(input => t.data:items) item

-- Current date/timeCURRENT_DATE(), CURRENT_TIMESTAMP()
-- Date arithmeticDATE_ADD(date_column, INTERVAL 7 DAY)DATE_SUB(date_column, INTERVAL 1 MONTH)DATE_DIFF(end_date, start_date, DAY)TIMESTAMP_DIFF(end_ts, start_ts, HOUR)
-- Truncate to periodDATE_TRUNC(created_at, MONTH)TIMESTAMP_TRUNC(created_at, HOUR)
-- Extract partsEXTRACT(YEAR FROM created_at)EXTRACT(DAYOFWEEK FROM created_at)  -- 1=Sunday
-- FormatFORMAT_DATE('%Y-%m-%d', date_column)FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', ts_column)

-- No ILIKE, use LOWER()LOWER(column) LIKE '%pattern%'REGEXP_CONTAINS(column, r'pattern')REGEXP_EXTRACT(column, r'pattern')
-- String manipulationSPLIT(str, delimiter)  -- returns ARRAYARRAY_TO_STRING(array, delimiter)

-- Array operationsARRAY_AGG(column)UNNEST(array_column)ARRAY_LENGTH(array_column)value IN UNNEST(array_column)
-- Struct accessstruct_column.field_name

-- Current date/timeCURRENT_DATE, GETDATE(), SYSDATE
-- Date arithmeticDATEADD(day, 7, date_column)DATEDIFF(day, start_date, end_date)
-- Truncate to periodDATE_TRUNC('month', created_at)
-- Extract partsEXTRACT(YEAR FROM created_at)DATE_PART('dow', created_at)

-- Case-insensitivecolumn ILIKE '%pattern%'REGEXP_INSTR(column, 'pattern') > 0
-- String manipulationSPLIT_PART(str, delimiter, position)LISTAGG(column, ', ') WITHIN GROUP (ORDER BY column)

-- Current date/timeCURRENT_DATE(), CURRENT_TIMESTAMP()
-- Date arithmeticDATE_ADD(date_column, 7)DATEDIFF(end_date, start_date)ADD_MONTHS(date_column, 1)
-- Truncate to periodDATE_TRUNC('MONTH', created_at)TRUNC(date_column, 'MM')
-- Extract partsYEAR(created_at), MONTH(created_at)DAYOFWEEK(created_at)

-- Time travelSELECT * FROM my_table TIMESTAMP AS OF '2024-01-15'SELECT * FROM my_table VERSION AS OF 42
-- Describe historyDESCRIBE HISTORY my_table
-- Merge (upsert)MERGE INTO target USING sourceON target.id = source.idWHEN MATCHED THEN UPDATE SET *WHEN NOT MATCHED THEN INSERT *

-- RankingROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)RANK() OVER (PARTITION BY category ORDER BY revenue DESC)DENSE_RANK() OVER (ORDER BY score DESC)
-- Running totals / moving averagesSUM(revenue) OVER (ORDER BY date_col ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_totalAVG(revenue) OVER (ORDER BY date_col ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d
-- Lag / LeadLAG(value, 1) OVER (PARTITION BY entity ORDER BY date_col) as prev_valueLEAD(value, 1) OVER (PARTITION BY entity ORDER BY date_col) as next_value
-- First / Last valueFIRST_VALUE(status) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)LAST_VALUE(status) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
-- Percent of totalrevenue / SUM(revenue) OVER () as pct_of_totalrevenue / SUM(revenue) OVER (PARTITION BY category) as pct_of_category

WITH-- Step 1: Define the base populationbase_users AS (    SELECT user_id, created_at, plan_type    FROM users    WHERE created_at >= DATE '2024-01-01'      AND status = 'active'),
-- Step 2: Calculate user-level metricsuser_metrics AS (    SELECT        u.user_id,        u.plan_type,        COUNT(DISTINCT e.session_id) as session_count,        SUM(e.revenue) as total_revenue    FROM base_users u    LEFT JOIN events e ON u.user_id = e.user_id    GROUP BY u.user_id, u.plan_type),
-- Step 3: Aggregate to summary levelsummary AS (    SELECT        plan_type,        COUNT(*) as user_count,        AVG(session_count) as avg_sessions,        SUM(total_revenue) as total_revenue    FROM user_metrics    GROUP BY plan_type)
SELECT * FROM summary ORDER BY total_revenue DESC;

WITH cohorts AS (    SELECT        user_id,        DATE_TRUNC('month', first_activity_date) as cohort_month    FROM users),activity AS (    SELECT        user_id,        DATE_TRUNC('month', activity_date) as activity_month    FROM user_activity)SELECT    c.cohort_month,    COUNT(DISTINCT c.user_id) as cohort_size,    COUNT(DISTINCT CASE        WHEN a.activity_month = c.cohort_month THEN a.user_id    END) as month_0,    COUNT(DISTINCT CASE        WHEN a.activity_month = c.cohort_month + INTERVAL '1 month' THEN a.user_id    END) as month_1,    COUNT(DISTINCT CASE        WHEN a.activity_month = c.cohort_month + INTERVAL '3 months' THEN a.user_id    END) as month_3FROM cohorts cLEFT JOIN activity a ON c.user_id = a.user_idGROUP BY c.cohort_monthORDER BY c.cohort_month;

WITH funnel AS (    SELECT        user_id,        MAX(CASE WHEN event = 'page_view' THEN 1 ELSE 0 END) as step_1_view,        MAX(CASE WHEN event = 'signup_start' THEN 1 ELSE 0 END) as step_2_start,        MAX(CASE WHEN event = 'signup_complete' THEN 1 ELSE 0 END) as step_3_complete,        MAX(CASE WHEN event = 'first_purchase' THEN 1 ELSE 0 END) as step_4_purchase    FROM events    WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'    GROUP BY user_id)SELECT    COUNT(*) as total_users,    SUM(step_1_view) as viewed,    SUM(step_2_start) as started_signup,    SUM(step_3_complete) as completed_signup,    SUM(step_4_purchase) as purchased,    ROUND(100.0 * SUM(step_2_start) / NULLIF(SUM(step_1_view), 0), 1) as view_to_start_pct,    ROUND(100.0 * SUM(step_3_complete) / NULLIF(SUM(step_2_start), 0), 1) as start_to_complete_pct,    ROUND(100.0 * SUM(step_4_purchase) / NULLIF(SUM(step_3_complete), 0), 1) as complete_to_purchase_pctFROM funnel;

-- Keep the most recent record per keyWITH 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;