5 Underrated Advanced DuckDB SQL Techniques That Double Your Analysis Efficiency

import duckdb

con = duckdb.connect()

# Create sample data
con.execute("""
CREATE TABLE orders AS
SELECT * FROM (VALUES
 ('Alice', 'electronics', 1200),
 ('Alice', 'books', 150),
 ('Bob', 'clothing', 300),
 ('Bob', 'electronics', 800),
 ('Bob', 'books', 200),
 ('Charlie', 'clothing', 500),
 ('Charlie', 'food', 80)
) t(name, category, amount);
""")

# Core: MAP_AGG builds category-to-amount mapping
result = con.execute("""
SELECT
 name,
 MAP_AGG(category, amount) as category_spending
FROM orders
GROUP BY name
""").fetchdf()

print(result)
# name category_spending
# 0 Alice {books: 150, electronics: 1200}
# 1 Bob {books: 200, clothing: 300, electronics: 800}
# 2 Charlie {clothing: 500, food: 80}

# Get the highest-spending category per user
result = con.execute("""
SELECT
 name,
 MAP_KEYS(category_spending)[0] as top_category
FROM (
 SELECT
 name,
 MAP_AGG(category, amount) as category_spending,
 MAX(amount) as max_amount
 FROM orders
 GROUP BY name
)
""").fetchdf()

# Expand MAP entries into rows for further filtering
result = con.execute("""
SELECT
 name,
 entry.key as category,
 entry.value as amount
FROM (
 SELECT name, MAP_AGG(category, amount) as category_spending
 FROM orders GROUP BY name
),
UNNEST(MAP_ENTRIES(category_spending)) AS entry
ORDER BY name, amount DESC
""").fetchdf()

import duckdb

con = duckdb.connect()

# Create table directly from JSON file
con.execute("CREATE TABLE api_json AS SELECT * FROM read_json_auto(['data.json'])")

# Or query from string directly
con.execute("""
CREATE TABLE orders_raw AS VALUES ('{
 "user_id": 1,
 "items": [
 {"name": "laptop", "price": 999, "qty": 1},
 {"name": "mouse", "price": 29, "qty": 2}
 ]
}'::VARCHAR
''')

# Core: json_extract_scalar + UNNEST flattens nested arrays
result = con.execute("""
SELECT
 json_extract_scalar(column1, '$.user_id') as user_id,
 item.name as item_name,
 json_extract_int(item, '$.price') as price,
 json_extract_int(item, '$.qty') as qty,
 json_extract_int(item, '$.price') * json_extract_int(item, '$.qty') as total
FROM orders_raw,
UNNEST(json_extract_array(column1, '$.items')) AS item
""").fetchdf()

print(result)

# Explore structure with json_tree
con.execute("""
CREATE TABLE deep_json AS VALUES ('{
 "company": "TechCorp",
 "departments": [
 {"name": "Engineering", "teams": [
 {"name": "Backend", "headcount": 15, "budget": 500000},
 {"name": "Frontend", "headcount": 8, "budget": 300000}
 ]},
 {"name": "Sales", "teams": [
 {"name": "Enterprise", "headcount": 10, "budget": 400000}
 ]}
 ]
}')
""")

# Recursively expand all team information
result = con.execute("""
SELECT
 dept.value->>'$.name' as department,
 team.value->>'$.name' as team_name,
 json_extract_int(team.value, '$.headcount') as headcount,
 json_extract_int(team.value, '$.budget') as budget
FROM deep_json,
UNNEST(json_extract_array(deep_json.column1, '$.departments')) AS dept,
UNNEST(json_extract_array(dept.value, '$.teams')) AS team
""").fetchdf()

import duckdb

con = duckdb.connect()

# Organization chart data
con.execute("""
CREATE TABLE org_chart AS
SELECT * FROM (VALUES
 (1, NULL, 'CEO'),
 (2, 1, 'VP Engineering'),
 (3, 1, 'VP Sales'),
 (4, 2, 'Engineering Manager'),
 (5, 2, 'Senior Developer'),
 (6, 4, 'Junior Developer'),
 (7, 4, 'QA Engineer'),
 (8, 3, 'Sales Manager'),
 (9, 8, 'Sales Rep')
) t(emp_id, manager_id, title);
""")

# Recursive CTE: calculate reporting hierarchy level
result = con.execute("""
WITH RECURSIVE org_hierarchy AS (
 -- Base case: top-level managers
 SELECT
 emp_id,
 manager_id,
 title,
 1 as level,
 CAST(title AS VARCHAR) as path
 FROM org_chart
 WHERE manager_id IS NULL
 
 UNION ALL
 
 -- Recursive case: subordinate employees
 SELECT
 c.emp_id,
 c.manager_id,
 c.title,
 o.level + 1,
 o.path || ' -> ' || c.title
 FROM org_chart c
 JOIN org_hierarchy o ON c.manager_id = o.emp_id
)
SELECT * FROM org_hierarchy
ORDER BY level, emp_id
""").fetchdf()

print(result)

# Product BOM data
con.execute("""
CREATE TABLE bom AS
SELECT * FROM (VALUES
 ('A', NULL, 'Product A', 1, 100),
 ('B', 'A', 'Sub-Assembly B', 2, 30),
 ('C', 'A', 'Component C', 1, 15),
 ('D', 'B', 'Part D', 3, 5),
 ('E', 'B', 'Part E', 2, 8),
 ('F', 'D', 'Raw Material F', 1, 2)
) t(part_id, parent_id, part_name, qty_per_parent, unit_cost);
""")

# Calculate final cost for each part (including all sub-component costs)
result = con.execute("""
WITH RECURSIVE cost_rollup AS (
 -- Base: parts with no children
 SELECT
 part_id,
 parent_id,
 part_name,
 qty_per_parent,
 unit_cost,
 unit_cost * qty_per_parent as direct_cost,
 ARRAY[part_id] as ancestry
 FROM bom
 WHERE parent_id IS NULL
 
 UNION ALL
 
 -- Recursive: parts with parents
 SELECT
 b.part_id,
 b.parent_id,
 b.part_name,
 b.qty_per_parent,
 b.unit_cost,
 b.unit_cost * b.qty_per_parent * cr.qty_per_parent as direct_cost,
 cr.ancestry || b.part_id
 FROM bom b
 JOIN cost_rollup cr ON b.parent_id = cr.part_id
)
SELECT * FROM cost_rollup
""").fetchdf()

import duckdb

con = duckdb.connect()

# Generate mock large dataset
con.execute("""
CREATE TABLE sales AS
SELECT
 gen as sale_id,
 DATE('2024-01-01') + (gen % 365) as sale_date,
 ('Alice','Bob','Charlie','Diana','Eve')[1 + (gen % 5)] as salesperson,
 ('Electronics','Clothing','Books','Food','Home')[1 + (gen % 5)] as category,
 ROUND(10 + (gen % 500)::FLOAT, 2) as amount
FROM generate_series(1, 1000000) AS t(gen);
""")

# BERNOULLI: Row-based random sampling (most statistically accurate)
sample_bernoulli = con.execute("""
SELECT * FROM sales TABLESAMPLE BERNOULLI (1%)
LIMIT 10
""").fetchdf()
print("BERNOULLI Sample:")
print(sample_bernoulli)

# SYSTEM: Block-based sampling (fastest speed)
sample_system = con.execute("""
SELECT * FROM sales TABLESAMPLE SYSTEM (1%)
LIMIT 10
""").fetchdf()
print("\nSYSTEM Sample:")
print(sample_system)

# RESERVOIR: Without-replacement random sampling (great for streaming data)
sample_reservoir = con.execute("""
SELECT * FROM sales TABLESAMPLE RESERVOIR (100 ROWS)
""").fetchdf()
print(f"\nRESERVOIR ({len(sample_reservoir)} rows):")
print(sample_reservoir.head())

def run_analysis(query_template, sample_size='1%', full=False):
 """Utility function for rapid query logic validation"""
 if full:
 query = f"WITH sampled AS (\n{query_template}\n)\nSELECT * FROM sampled"
 else:
 # Insert TABLESAMPLE after FROM clause
 from_idx = query_template.upper().index('FROM')
 table_end = query_template.find(' ', from_idx + 4)
 
 query = (query_template[:table_end] +
 f" TABLESAMPLE BERNOULLI ({sample_size}) " +
 query_template[table_end:])
 
 return con.execute(query).fetchdf()

# Example: Validate complex query with 0.1% sample first
result = run_analysis("""
SELECT
 salesperson,
 category,
 COUNT(*) as order_count,
 SUM(amount) as total_revenue,
 AVG(amount) as avg_order_value
FROM sales
GROUP BY salesperson, category
HAVING SUM(amount) > 1000
ORDER BY total_revenue DESC
""", sample_size='0.1%')
print(result)

# After confirming correctness, run full
full_result = run_analysis("""
SELECT
 salesperson,
 category,
 COUNT(*) as order_count,
 SUM(amount) as total_revenue,
 AVG(amount) as avg_order_value
FROM sales
GROUP BY salesperson, category
HAVING SUM(amount) > 1000
ORDER BY total_revenue DESC
""", full=True)
print(full_result)

import duckdb
import os

con = duckdb.connect()

# Create sample data
con.execute("""
CREATE TABLE monthly_report AS
SELECT 'Q1' as quarter, 'Electronics' as category, 150000 as revenue, 25000 as profit
UNION ALL
SELECT 'Q1', 'Clothing', 80000, 12000
UNION ALL
SELECT 'Q2', 'Electronics', 180000, 30000
UNION ALL
SELECT 'Q2', 'Clothing', 95000, 15000
""")

# 1. Export as Parquet (recommended — high compression, preserves types)
con.execute("COPY monthly_report TO 'output/report.parquet' (FORMAT PARQUET)")
print("Parquet exported")

# 2. Export as CSV (universal format)
con.execute("COPY monthly_report TO 'output/report.csv' (HEADER, DELIMITER ',')")
print("CSV exported")

# 3. Export as JSON (ideal for API transmission)
con.execute("COPY monthly_report TO 'output/report.json' (FORMAT JSON)")
print("JSON exported")

import duckdb
import zipfile
from datetime import datetime

def generate_client_report(client_id, output_dir='deliverables'):
 """Generate complete analysis report package for each client"""
 
 con = duckdb.connect(':memory:')
 
 # 1. Query analysis results
 summary = con.execute(f"""
 SELECT
 month,
 SUM(revenue) as total_revenue,
 SUM(profit) as total_profit,
 ROUND(AVG(margin), 2) as avg_margin
 FROM client_{client_id}_sales
 GROUP BY month
 ORDER BY month
 """).fetchdf()
 
 # 2. Generate multiple formats
 os.makedirs(output_dir, exist_ok=True)
 timestamp = datetime.now().strftime('%Y%m%d')
 
 # Parquet for further analysis
 con.execute(f"COPY (SELECT * FROM client_{client_id}_sales) TO '{output_dir}/client_{client_id}_{timestamp}.parquet'")
 
 # CSV for Excel viewing
 con.execute(f"COPY (SELECT * FROM client_{client_id}_sales) TO '{output_dir}/client_{client_id}_{timestamp}.csv' (HEADER)")
 
 # JSON for API integration
 con.execute(f"COPY {summary} TO '{output_dir}/client_{client_id}_summary_{timestamp}.json' (FORMAT JSON)")
 
 # 3. Package for delivery
 zip_path = f'{output_dir}/client_{client_id}_{timestamp}.zip'
 with zipfile.ZipFile(zip_path, 'w') as zf:
 for file in os.listdir(output_dir):
 if file.startswith(f'client_{client_id}_{timestamp}'):
 zf.write(os.path.join(output_dir, file), file)
 
 print(f"Report package delivered: {zip_path}")
 return zip_path

# One-click delivery for multiple clients
for client in ['acme_corp', 'globex_inc', 'initech']:
 generate_client_report(client)