-- Standard table creation
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR,
    salary DECIMAL(10,2),
    hire_date DATE
);

-- Create from query results
CREATE TABLE high_earners AS
SELECT * FROM employees WHERE salary > 100000;

-- Temporary table (auto-deleted at session end)
CREATE TEMP TABLE temp_results AS
SELECT department, AVG(salary) AS avg_salary
FROM employees GROUP BY department;

-- Add column
ALTER TABLE employees ADD COLUMN email VARCHAR;

-- Drop column
ALTER TABLE employees DROP COLUMN email;

-- Rename column
ALTER TABLE employees RENAME COLUMN salary TO base_salary;

SELECT name, department, salary
FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC
LIMIT 10;

-- GROUP BY ALL: auto-group by all non-aggregated columns
SELECT department, year(hire_date) AS hire_year, COUNT(*)
FROM employees
GROUP BY ALL;
-- Equivalent to GROUP BY department, year(hire_date)

-- COLUMNS(): apply same expression to multiple columns
SELECT COLUMNS('salary|bonus') * 1.1 AS salary_increase
FROM employees;

-- EXCLUDE: omit specific columns
SELECT * EXCLUDE (salary, ssn)
FROM employees;

-- REPLACE: override column expressions
SELECT * REPLACE (salary * 1.1 AS salary)
FROM employees;

-- Single row
INSERT INTO employees VALUES (5, 'Eve', 'Engineering', 130000, '2026-01-15');

-- Multiple rows
INSERT INTO employees VALUES
    (6, 'Frank', 'Sales', 90000, '2026-02-01'),
    (7, 'Grace', 'Marketing', 95000, '2026-02-15');

-- Insert from query
INSERT INTO high_earners
SELECT * FROM employees WHERE salary > 100000;

-- Insert from file (DuckDB speciality)
INSERT INTO employees
SELECT * FROM read_csv_auto('new_employees.csv');

-- Update
UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Engineering' AND salary < 100000;

-- Delete
DELETE FROM employees WHERE id = 5;

SELECT
    department,
    COUNT(*) AS employee_count,
    SUM(salary) AS total_salary,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 1
ORDER BY avg_salary DESC;

-- Median
SELECT department, MEDIAN(salary) AS median_salary
FROM employees GROUP BY department;

-- Percentiles
SELECT department,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) AS q1,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) AS q3
FROM employees GROUP BY department;

-- Statistical functions
SELECT department,
    AVG(salary) AS mean,
    STDDEV(sample(salary)) AS stddev,
    SKEWNESS(salary) AS skew,
    KURTOSIS(salary) AS kurt
FROM employees GROUP BY department;

SELECT
    name,
    department,
    salary,
    -- Ranking
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    -- Running total
    SUM(salary) OVER (PARTITION BY department ORDER BY name) AS running_total,
    -- Global aggregation
    AVG(salary) OVER () AS company_avg,
    -- Difference from average
    salary - AVG(salary) OVER () AS diff_from_avg
FROM employees;

SELECT
    date,
    amount,
    -- 3-day moving average
    AVG(amount) OVER (
        ORDER BY date
        ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3d,
    -- Year-to-date cumulative
    SUM(amount) OVER (
        PARTITION BY year(date)
        ORDER BY date
    ) AS ytd_total
FROM daily_sales;

SELECT
    department,
    AVG(salary) AS avg_all,
    AVG(salary) FILTER (WHERE salary > 100000) AS avg_high_only
FROM employees
GROUP BY department;

WITH department_stats AS (
    SELECT
        department,
        AVG(salary) AS avg_dept_salary
    FROM employees
    GROUP BY department
)
SELECT
    e.name,
    e.department,
    e.salary,
    d.avg_dept_salary,
    e.salary - d.avg_dept_salary AS salary_diff
FROM employees e
JOIN department_stats d ON e.department = d.department
WHERE e.salary > d.avg_dept_salary
ORDER BY salary_diff DESC;

-- Generate date series
WITH RECURSIVE dates AS (
    SELECT '2026-01-01'::DATE AS date
    UNION ALL
    SELECT date + 1
    FROM dates
    WHERE date < '2026-01-31'
)
SELECT * FROM dates;

-- Organizational tree query
WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, 1 AS level
    FROM org_chart
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM org_chart e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY level, name;

-- UNION (deduplicated)
SELECT name, department FROM current_employees
UNION
SELECT name, department FROM former_employees
ORDER BY name;

-- UNION ALL (faster, keeps duplicates)
SELECT region, revenue FROM sales_q1
UNION ALL
SELECT region, revenue FROM sales_q2;

-- INTERSECT and EXCEPT
SELECT product FROM products_2025
INTERSECT
SELECT product FROM products_2026;

SELECT product FROM products_2025
EXCEPT
SELECT product FROM products_2026;

-- Pivot department salary stats into columns
PIVOT employees
ON department
USING AVG(salary) AS avg_salary,
      COUNT(*) AS count
GROUP BY hire_year;

-- Using SQL standard syntax
SELECT *
FROM (SELECT department, salary FROM employees)
PIVOT (
    AVG(salary)
    FOR department IN ('Engineering', 'Sales', 'Marketing')
) AS p;

UNPIVOT quarterly_sales
ON q1, q2, q3, q4
INTO
    NAME quarter
    VALUE revenue;

-- List operations
SELECT [1, 2, 3] AS numbers,
       list_value(1, 2, 3) AS also_numbers,
       list_sort([3, 1, 2]) AS sorted;

-- Structs
SELECT {'name': 'Alice', 'salary': 120000} AS employee,
       (employee).name AS name;

-- UNNEST: flatten nested data
SELECT name, unnest(skills) AS skill
FROM (VALUES ('Alice', ['SQL', 'Python', 'Java'])) AS t(name, skills);

SELECT
    CURRENT_DATE AS today,
    DATE_TRUNC('month', '2026-05-21'::DATE) AS month_start,
    DATE_DIFF('month', '2026-01-01'::DATE, '2026-12-31'::DATE) AS months_diff,
    DATE_ADD('2026-01-01'::DATE, INTERVAL 3 MONTH) AS three_months_later,
    EXTRACT(YEAR FROM '2026-05-21'::DATE) AS year;

SELECT
    UPPER('hello') AS upper,
    LOWER('HELLO') AS lower,
    LENGTH('DuckDB') AS len,
    CONCAT('Hello', ' ', 'DuckDB') AS greeting,
    SPLIT_PART('a,b,c', ',', 2) AS second,
    REGEXP_MATCHES('[email protected]', '\w+@\w+\.\w+') AS is_email;

-- 1. Use EXPLAIN to view execution plans
EXPLAIN SELECT * FROM employees WHERE department = 'Engineering';

-- 2. Create indexes for filtering
CREATE INDEX idx_emp_dept ON employees(department);

-- 3. Limit parallelism
SET threads = 4;

-- 4. Adjust memory limits
SET memory_limit = '8GB';

-- 5. Prefer Parquet over CSV
COPY (SELECT * FROM employees) TO 'employees.parquet' (FORMAT PARQUET);