DuckDB 5 Advanced SQL Techniques: The Complete Guide to Doubling Performance

SELECT * FROM (
 SELECT *, 
 ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) as rn
 FROM sales
) WHERE rn <= 3

SELECT category, product, revenue
FROM sales
QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) <= 3

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, ot.level + 1
 FROM employees e
 JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, name

WITH RECURSIVE approval_chain AS (
 -- Starting approval node
 SELECT 
 task_id,
 approver_id,
 approver_name,
 status,
 1 as approval_level,
 CAST(approver_name AS VARCHAR) as chain
 FROM approvals
 WHERE task_id = 'ORDER_2024_001'
 
 UNION ALL
 
 -- Recursively trace next approval level
 SELECT 
 a.task_id,
 a.approver_id,
 a.approver_name,
 a.status,
 ac.approval_level + 1,
 ac.chain || ' → ' || a.approver_name
 FROM approvals a
 JOIN approval_chain ac ON a.prev_approver_id = ac.approver_id
)
SELECT * FROM approval_chain ORDER BY approval_level

EXPLAIN ANALYZE
SELECT 
 DATE_TRUNC('month', order_date) as month,
 category,
 SUM(amount) as revenue
FROM sales
WHERE order_date >= '2025-01-01'
GROUP BY 1, 2
ORDER BY 1, 2

EXPLAIN ANALYZE
SELECT 
 customer_id,
 SUM(amount) as total_spend,
 COUNT(*) as order_count
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
HAVING COUNT(*) > 10

-- Create temporary view (valid within session)
CREATE TEMP VIEW v_high_value_customers AS
SELECT 
 customer_id,
 SUM(amount) as total_spend,
 COUNT(*) as order_count,
 AVG(amount) as avg_order_value
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 10000;

-- Reuse temporary view
SELECT 
 c.customer_name,
 v.total_spend,
 v.order_count
FROM customers c
JOIN v_high_value_customers v ON c.id = v.customer_id
ORDER BY v.total_spend DESC

-- Step 1: Create temporary table for intermediate results
CREATE TEMP TABLE daily_metrics AS
SELECT 
 DATE_TRUNC('day', order_date) as date,
 region,
 COUNT(*) as order_count,
 SUM(amount) as revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY 1, 2;

-- Step 2: Further analysis based on temporary table
SELECT 
 date,
 region,
 revenue,
 LAG(revenue) OVER (PARTITION BY region ORDER BY date) as prev_day_revenue,
 ROUND((revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY date)) / 
 LAG(revenue) OVER (PARTITION BY region ORDER BY date) * 100, 2) as growth_pct
FROM daily_metrics
ORDER BY date, region;

import duckdb

# Register custom aggregate function
def weighted_avg(values, weights):
 """Calculate weighted average"""
 if not values or not weights:
 return None
 total_weight = sum(weights)
 if total_weight == 0:
 return None
 weighted_sum = sum(v * w for v, w in zip(values, weights))
 return weighted_sum / total_weight

con = duckdb.connect(':memory:')
con.create_function('weighted_avg', weighted_avg, ['DOUBLE', 'DOUBLE'])

# Use custom function
result = con.execute("""
 SELECT weighted_avg(amount, weight) 
 FROM sales
""").fetchone()
print(f"Weighted average sales: {result[0]}")

import duckdb
import pandas as pd

def extract_domain(email):
 """Extract domain from email"""
 if pd.isna(email):
 return None
 return email.split('@')[-1]

con = duckdb.connect(':memory:')
con.create_function('extract_domain', extract_domain, ['VARCHAR'])

# Use custom function
result = con.execute("""
 SELECT extract_domain(email) as domain, COUNT(*) 
 FROM customers 
 GROUP BY domain 
 ORDER BY COUNT(*) DESC
""").fetchall()

import duckdb

class CoefficientOfVariation:
 """Custom aggregate: Coefficient of Variation (std_dev/mean)"""
 
 def __init__(self):
 self.sum = 0.0
 self.sum_sq = 0.0
 self.count = 0
 
 def update(self, value):
 self.sum += value
 self.sum_sq += value * value
 self.count += 1
 
 def combine(self, other):
 self.sum += other.sum
 self.sum_sq += other.sum_sq
 self.count += other.count
 
 def finalize(self):
 if self.count < 2:
 return None
 mean = self.sum / self.count
 variance = (self.sum_sq / self.count) - (mean * mean)
 if variance < 0:
 return None
 std_dev = variance ** 0.5
 return std_dev / mean if mean != 0 else None

con = duckdb.connect(':memory:')
con.create_aggregate(CoefficientOfVariation, 'coeff_variation', ['DOUBLE'])

# Use custom aggregate
result = con.execute("""
 SELECT region, 
 coeff_variation(amount) as cv
 FROM sales
 GROUP BY region
 ORDER BY cv DESC
""").fetchall()

-- Step 1: Create temporary view to organize logic
CREATE TEMP VIEW v_customer_metrics AS
SELECT 
 c.customer_id,
 c.customer_name,
 COUNT(o.order_id) as order_count,
 SUM(o.amount) as total_spend,
 AVG(o.amount) as avg_order_value,
 MIN(o.order_date) as first_order_date,
 MAX(o.order_date) as last_order_date
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

-- Step 2: Use recursive CTE to calculate customer lifecycle
WITH RECURSIVE customer_journey AS (
 -- Anchor: First order
 SELECT 
 customer_id,
 first_order_date as event_date,
 'first_order' as event_type,
 1 as stage
 FROM v_customer_metrics
 
 UNION ALL
 
 -- Recursive: Subsequent stages (simplified here, can be extended)
 SELECT 
 customer_id,
 last_order_date as event_date,
 'last_order' as event_type,
 2 as stage
 FROM v_customer_metrics
)
SELECT * FROM customer_journey ORDER BY customer_id, event_date;

-- Step 3: Use QUALIFY to filter top 10 high-value customers
SELECT 
 customer_id,
 customer_name,
 total_spend,
 order_count,
 ROUND(total_spend / NULLIF(order_count, 0), 2) as avg_order_value
FROM v_customer_metrics
QUALIFY ROW_NUMBER() OVER (ORDER BY total_spend DESC) <= 10
ORDER BY total_spend DESC;

-- Step 4: Use EXPLAIN ANALYZE to verify performance
EXPLAIN ANALYZE
SELECT 
 customer_id,
 customer_name,
 total_spend,
 order_count
FROM v_customer_metrics
WHERE total_spend > 10000
ORDER BY total_spend DESC;