1-- Assume a table: events(id INT, payload VARIANT)
2-- where payload contains: {"user": {"id": 42, "name": "Alice"}, "tags": ["a","b"]}
3
4-- Basic path access — returns VARIANT
5SELECT payload:user:name FROM events;
6
7-- Cast to a specific SQL type using ::
8SELECT
9payload:user:id::NUMBER    AS user_id,
10payload:user:name::STRING  AS user_name,
11payload:active::BOOLEAN    AS is_active
12FROM events;
13
14-- GET() function — equivalent to : operator
15SELECT GET(payload, 'user') FROM events;
16
17-- GET_PATH() function — dot-notation path
18SELECT GET_PATH(payload, 'user.name')::STRING FROM events;
19
20-- TYPEOF() — inspect the internal data type of a VARIANT value
21SELECT
22TYPEOF(payload:user:id)    AS id_type,    -- returns 'integer'
23TYPEOF(payload:user:name)  AS name_type,  -- returns 'string'
24TYPEOF(payload:tags)       AS tags_type   -- returns 'array'
25FROM events;
26
27-- PARSE_JSON() — convert a string to VARIANT
28SELECT PARSE_JSON('{"key": "value", "count": 99}') AS parsed;
29
30-- OBJECT_CONSTRUCT() — build a VARIANT object from key-value pairs
31SELECT OBJECT_CONSTRUCT('id', 1, 'name', 'Alice', 'score', 99.5) AS obj;
32
33-- ARRAY_CONSTRUCT() — build a VARIANT array
34SELECT ARRAY_CONSTRUCT(10, 20, 30, 'text', NULL) AS arr;

1-- Table: users(id INT, profile VARIANT)
2-- profile: {"name": "Bob", "tags": ["admin","editor","viewer"]}
3
4-- Explode the tags array — one row per tag
5SELECT
6u.id,
7u.profile:name::STRING AS user_name,
8f.value::STRING        AS tag,
9f.index                AS tag_position
10FROM users u,
11LATERAL FLATTEN(INPUT => u.profile:tags) f;
12
13-- FLATTEN columns available:
14-- f.VALUE  — the element value (VARIANT)
15-- f.KEY    — the key name (for objects, NULL for arrays)
16-- f.INDEX  — the zero-based position in the array
17-- f.PATH   — full path to the element from the root
18-- f.SEQ    — sequence number unique per input row
19-- f.THIS   — the containing array/object
20
21-- Flattening an OBJECT (key-value pairs)
22SELECT f.key, f.value::STRING
23FROM (SELECT PARSE_JSON('{"a":1,"b":2,"c":3}') AS obj),
24LATERAL FLATTEN(INPUT => obj) f;
25
26-- OUTER => TRUE — keep rows even when the array is NULL or empty
27SELECT u.id, f.value::STRING AS tag
28FROM users u,
29LATERAL FLATTEN(INPUT => u.profile:tags, OUTER => TRUE) f;
30
31-- RECURSIVE => TRUE — flatten nested arrays/objects recursively
32SELECT f.path, f.value
33FROM (SELECT PARSE_JSON('{"a":{"b":{"c":42}}}') AS nested),
34LATERAL FLATTEN(INPUT => nested, RECURSIVE => TRUE) f;

1-- ROW_NUMBER(): unique sequential number per partition
2SELECT
3order_id,
4customer_id,
5order_date,
6ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
7FROM orders;
8
9-- RANK(): same rank for ties, then skips numbers (1,1,3,4...)
10SELECT
11product_id, sales,
12RANK() OVER (ORDER BY sales DESC) AS sales_rank
13FROM product_sales;
14
15-- DENSE_RANK(): same rank for ties, no gaps (1,1,2,3...)
16SELECT
17product_id, sales,
18DENSE_RANK() OVER (ORDER BY sales DESC) AS dense_rank
19FROM product_sales;
20
21-- LEAD() and LAG() — access rows ahead of or behind the current row
22SELECT
23order_date,
24revenue,
25LAG(revenue, 1, 0)  OVER (ORDER BY order_date) AS prev_day_revenue,
26LEAD(revenue, 1, 0) OVER (ORDER BY order_date) AS next_day_revenue,
27revenue - LAG(revenue, 1, 0) OVER (ORDER BY order_date) AS day_over_day_change
28FROM daily_revenue;
29
30-- FIRST_VALUE() and LAST_VALUE() within a window frame
31SELECT
32department,
33employee,
34salary,
35FIRST_VALUE(salary) OVER (
36  PARTITION BY department ORDER BY salary DESC
37  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
38) AS dept_max_salary,
39LAST_VALUE(salary)  OVER (
40  PARTITION BY department ORDER BY salary DESC
41  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
42) AS dept_min_salary
43FROM employees;
44
45-- Running total and moving average
46SELECT
47order_date,
48daily_sales,
49SUM(daily_sales)  OVER (ORDER BY order_date
50  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
51AVG(daily_sales)  OVER (ORDER BY order_date
52  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)          AS seven_day_avg
53FROM daily_sales;

1-- Get the most recent order per customer (deduplication)
2-- WITHOUT QUALIFY — requires subquery:
3SELECT * FROM (
4SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
5FROM orders
6) WHERE rn = 1;
7
8-- WITH QUALIFY — cleaner single-level query:
9SELECT *
10FROM orders
11QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1;
12
13-- Top 3 products per category by sales
14SELECT category, product_id, sales
15FROM product_sales
16QUALIFY DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC) <= 3;
17
18-- Remove duplicate rows keeping the row with highest id
19SELECT *
20FROM raw_events
21QUALIFY ROW_NUMBER() OVER (PARTITION BY event_key ORDER BY id DESC) = 1;
22
23-- QUALIFY with a WHERE clause — WHERE runs before QUALIFY
24SELECT *
25FROM orders
26WHERE order_status = 'completed'
27QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1;

1-- Source data: sales(region TEXT, month TEXT, amount NUMBER)
2-- rows: ('North','Jan',100), ('North','Feb',200), ('South','Jan',150)...
3
4-- PIVOT: rotate month values into columns
5SELECT *
6FROM sales
7PIVOT(SUM(amount) FOR month IN ('Jan', 'Feb', 'Mar', 'Apr'))
8AS p (region, jan_sales, feb_sales, mar_sales, apr_sales);
9-- Result: one row per region, columns jan_sales, feb_sales, mar_sales, apr_sales
10
11-- UNPIVOT: rotate columns back into rows
12-- Source: monthly_sales(region TEXT, jan NUMBER, feb NUMBER, mar NUMBER)
13SELECT region, month_name, monthly_amount
14FROM monthly_summary
15UNPIVOT(monthly_amount FOR month_name IN (jan, feb, mar));
16-- Result: three rows per region with month_name and monthly_amount
17
18-- Dynamic PIVOT using Snowflake scripting (for unknown category values)
19-- Step 1: get distinct months
20SET months = (
21SELECT '[''' || LISTAGG(DISTINCT month, ''',''') || ''']'
22FROM sales
23);
24-- Step 2: use in PIVOT (requires dynamic SQL via Stored Procedure in practice)
25
26-- ANY keyword for dynamic pivot (Snowflake 8.x+)
27SELECT *
28FROM sales
29PIVOT(SUM(amount) FOR month IN (ANY ORDER BY month));

1-- GENERATOR: produce synthetic rows
2-- ROWCOUNT => number of rows to generate
3-- SEQ4() or SEQ8() provide unique sequential integers
4SELECT SEQ4() AS id,
5     UNIFORM(1, 100, RANDOM())::INT    AS score,
6     DATEADD('day', SEQ4(), '2024-01-01')::DATE AS event_date
7FROM TABLE(GENERATOR(ROWCOUNT => 1000000))
8LIMIT 10;
9
10-- GENERATOR with TIMELIMIT — run for N seconds instead of fixed row count
11SELECT SEQ4() AS id, RANDOM() AS val
12FROM TABLE(GENERATOR(TIMELIMIT => 5));
13
14-- TABLESAMPLE: return a random sample of rows
15-- Percentage-based (block sampling — by micro-partition)
16SELECT * FROM large_table SAMPLE (10);   -- approx 10% of rows
17
18-- Row-count based sampling
19SELECT * FROM large_table SAMPLE (1000 ROWS);
20
21-- Bernoulli sampling (row-level — more random, slower)
22SELECT * FROM large_table SAMPLE BERNOULLI (5);
23
24-- Block sampling (micro-partition level — faster, less random)
25SELECT * FROM large_table SAMPLE BLOCK (5);
26
27-- Reproducible sample with SEED
28SELECT * FROM orders SAMPLE (10) SEED (42);

1-- TIME_SLICE: group timestamps into fixed intervals
2-- Syntax: TIME_SLICE(timestamp_col, slice_length, 'interval_unit')
3SELECT
4TIME_SLICE(event_ts, 15, 'MINUTE') AS fifteen_min_bucket,
5COUNT(*)                            AS event_count
6FROM events
7GROUP BY 1
8ORDER BY 1;
9
10-- Also supports HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
11SELECT TIME_SLICE(order_date, 1, 'MONTH') AS month_start FROM orders;
12
13-- DATE_TRUNC: truncate a timestamp to a specified granularity
14SELECT
15DATE_TRUNC('month',  CURRENT_TIMESTAMP),   -- first of current month
16DATE_TRUNC('week',   CURRENT_TIMESTAMP),   -- Monday of current week
17DATE_TRUNC('hour',   CURRENT_TIMESTAMP);   -- current hour start
18
19-- DATEADD: add/subtract intervals
20SELECT
21DATEADD('day',    7,  CURRENT_DATE),       -- 7 days from now
22DATEADD('month', -3,  CURRENT_DATE),       -- 3 months ago
23DATEADD('hour',  12,  CURRENT_TIMESTAMP);
24
25-- DATEDIFF: difference between two dates in specified unit
26SELECT
27DATEDIFF('day',   '2024-01-01', '2024-12-31') AS days_diff,    -- 364
28DATEDIFF('month', '2024-01-01', '2024-12-31') AS months_diff;  -- 11
29
30-- DATE_PART and EXTRACT: extract components
31SELECT
32DATE_PART('year',  CURRENT_DATE),
33DATE_PART('month', CURRENT_DATE),
34DATE_PART('dow',   CURRENT_DATE),        -- day of week (0=Sunday)
35EXTRACT(epoch FROM CURRENT_TIMESTAMP);   -- seconds since 1970-01-01
36
37-- Timezone conversion
38SELECT
39CONVERT_TIMEZONE('UTC', 'Europe/London', CURRENT_TIMESTAMP) AS london_time,
40TO_TIMESTAMP_NTZ('2024-06-01 12:00:00') AS no_timezone_ts;

1-- IFF: compact if-then-else (Snowflake-specific)
2SELECT IFF(salary > 50000, 'Senior', 'Junior') AS level FROM employees;
3
4-- CASE WHEN: standard SQL conditional
5SELECT
6CASE
7  WHEN score >= 90 THEN 'A'
8  WHEN score >= 80 THEN 'B'
9  WHEN score >= 70 THEN 'C'
10  ELSE 'F'
11END AS grade
12FROM student_scores;
13
14-- DECODE: value-based mapping (Oracle-style — Snowflake supports it)
15SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') FROM users;
16
17-- NULL handling functions
18SELECT
19NVL(commission, 0)           AS commission,       -- replace NULL with 0
20NVL2(commission, 'Yes', 'No') AS has_commission,  -- NVL2(col, not-null-val, null-val)
21NULLIF(revenue, 0)           AS safe_revenue,     -- return NULL if revenue = 0
22COALESCE(phone, mobile, email, 'none') AS contact,-- first non-null
23ZEROIFNULL(discount)         AS discount,         -- NULL -> 0
24NULLIFZERO(quantity)         AS quantity          -- 0 -> NULL
25FROM sales;
26
27-- String functions
28SELECT
29SPLIT('a,b,c,d', ',')                    AS arr,         -- returns ARRAY VARIANT
30SPLIT_PART('hello.world.test', '.', 2)   AS part,        -- 'world'
31TRIM('   hello   ')                      AS trimmed,
32LTRIM('---hello', '-')                   AS left_trim,
33CONTAINS('hello world', 'world')         AS found,       -- TRUE
34STARTSWITH('hello', 'hel')              AS sw,           -- TRUE
35ENDSWITH('hello', 'llo')               AS ew,            -- TRUE
36RPAD('abc', 6, '*')                     AS rpadded,      -- 'abc***'
37LPAD('42', 5, '0')                      AS lpadded,      -- '00042'
38CHARINDEX('world', 'hello world')        AS pos           -- 7
39FROM dual;

1-- Detect a 'V-shape' pattern in stock prices:
2-- a decrease followed by an increase
3SELECT *
4FROM stock_prices
5MATCH_RECOGNIZE (
6  PARTITION BY ticker
7  ORDER BY trade_date
8  MEASURES
9    MATCH_NUMBER()         AS match_num,
10    FIRST(trade_date)      AS start_date,
11    LAST(trade_date)       AS end_date,
12    FIRST(close_price)     AS start_price,
13    MIN(close_price)       AS bottom_price,
14    LAST(close_price)      AS end_price
15  ONE ROW PER MATCH
16  AFTER MATCH SKIP TO NEXT ROW
17  PATTERN (DOWN+ UP+)
18  DEFINE
19    DOWN AS close_price < LAG(close_price) OVER (ORDER BY trade_date),
20    UP   AS close_price > LAG(close_price) OVER (ORDER BY trade_date)
21);