DuckDB Advanced SQL Techniques: PIVOT, Macro, Window Functions, Recursive CTE & JSON Processing

-- Raw data: one row per quarter
SELECT * FROM sales;
-- product | quarter | amount
-- Apple | Q1 | 100
-- Apple | Q2 | 150
-- Banana | Q1 | 200
-- Banana | Q2 | 120

-- PIVOT: Turn quarters into columns
SELECT * FROM sales
PIVOT(sum(amount) FOR quarter IN ("Q1", "Q2"));
-- product | Q1 | Q2
-- Apple | 100 | 150
-- Banana | 200 | 120

SELECT * FROM pivot_sales
UNPIVOT(amount FOR quarter IN ("Q1", "Q2"));
-- product | quarter | amount
-- Apple | Q1 | 100
-- Apple | Q2 | 150
-- Banana | Q1 | 200
-- Banana | Q2 | 120

SELECT 
 category,
 "January" AS jan, "February" AS feb, "March" AS mar
FROM monthly_sales
PIVOT(
 SUM(revenue) 
 FOR month_name 
 IN ("January", "February", "March")
);

-- Define a macro to calculate squared difference
CREATE MACRO square(x) AS x * x;

SELECT square(5) AS result;
-- 25

SELECT square(col_a - col_b) AS diff_square
FROM transactions;

CREATE MACRO mom_growth(curr, prev)
 AS ROUND((curr - prev) * 1.0 / prev, 4);

SELECT 
 month,
 revenue,
 mom_growth(revenue, LAG(revenue) OVER(ORDER BY month)) AS growth
FROM monthly_sales;
-- month | revenue | growth
-- January | 10000 | NULL
-- February| 12000 | 0.2
-- March | 11000 | -0.0833

CREATE MACRO round_to(x, digits DEFAULT 2)
 AS ROUND(x, digits);

SELECT round_to(3.14159); -- 3.14
SELECT round_to(3.14159, 3); -- 3.142
SELECT round_to(100.12345, 1); -- 100.1

SELECT name, subject, score,
 LAG(score) OVER(PARTITION BY name ORDER BY score) AS prev_score
FROM scores;

-- name | subject | score | prev_score
-- Bob | Science | 88 | NULL
-- Bob | Math | 92 | 88
-- Alice | Math | 85 | NULL
-- Alice | Science | 90 | 85

SELECT DISTINCT name,
 FIRST_VALUE(score) OVER(
 PARTITION BY name ORDER BY score DESC
 ) AS best_score,
 LAST_VALUE(score) OVER(
 PARTITION BY name ORDER BY score ASC
 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 ) AS worst_score
FROM scores;

WITH ranked AS (
 SELECT 
 product_name,
 revenue,
 SUM(revenue) OVER(ORDER BY revenue DESC 
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,
 SUM(revenue) OVER() AS total
 FROM products
)
SELECT 
 product_name,
 revenue,
 ROUND(cumulative * 100.0 / total, 2) AS cumulative_pct
FROM ranked
ORDER BY revenue DESC;
-- This is the famous Pareto analysis (80/20 rule)

WITH base AS (
 -- Step 1: Filter valid orders
 SELECT id, amount, customer_id 
 FROM orders 
 WHERE status = 'completed'
),
ranked AS (
 -- Step 2: Calculate customer ranking
 SELECT 
 customer_id,
 SUM(amount) AS total_spend,
 ROW_NUMBER() OVER(ORDER BY SUM(amount) DESC) AS rn
 FROM base
 GROUP BY customer_id
),
top_customers AS (
 -- Step 3: Get Top 10
 SELECT * FROM ranked WHERE rn <= 10
)
SELECT * FROM top_customers;

WITH RECURSIVE org_tree AS (
 -- Anchor: top-level manager
 SELECT id, name, manager_id, 1 AS level
 FROM employees 
 WHERE manager_id IS NULL
 
 UNION ALL
 
 -- Recursive: subordinate employees
 SELECT e.id, e.name, e.manager_id, t.level + 1
 FROM employees e
 JOIN org_tree t ON e.manager_id = t.id
)
SELECT 
 LPAD('', (level-1)*4, ' ') || name AS org_chart,
 level
FROM org_tree
ORDER BY level, name;
-- Result example:
-- CEO
-- Engineering
-- Alice
-- Bob
-- Marketing
-- Charlie

-- Extract JSON fields
SELECT json_extract_string(
 '{"user": "alice", "age": 30, "tags": ["dev", "gopher"]}',
 '$.user'
) AS username;
-- alice

SELECT * FROM json_each('{
 "items": ["Apple", "Banana", "Cherry"]
}'::JSON -> 'items') AS j;
-- [{"value":"Apple"},{"value":"Banana"},{"value":"Cherry"}]

SELECT
 list_sort([3, 1, 2]) AS sorted, -- [1, 2, 3]
 list_reverse([1, 2, 3]) AS reversed, -- [3, 2, 1]
 list_distinct([1, 1, 2, 3]) AS uniq, -- [1, 2, 3]
 list_concat([1, 2], [3, 4]) AS joined, -- [1, 2, 3, 4]
 list_slice([1,2,3,4,5], 2, 4) AS sliced, -- [2, 3, 4]
 list_avg([10, 20, 30, 40]) AS average, -- 25.0
 list_generate(1, 10, 2) AS odds; -- [1, 3, 5, 7, 9]

-- Suppose an API returns a JSON array
WITH api_data AS (
 SELECT * FROM json_each('[
 {"id": 1, "name": "Alice", "scores": [90, 85, 92]},
 {"id": 2, "name": "Bob", "scores": [78, 88, 95]}
 ]')
)
SELECT 
 value::JSON ->> 'name' AS name,
 list_avg((value::JSON -> 'scores')::LIST) AS avg_score
FROM api_data;
-- Alice | 89.0
-- Bob | 87.0