SQL Dialect Translator Examples

WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', order_date) AS month,
        customer_id,
        SUM(total) AS monthly_total
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'
      AND status = 'completed'
    GROUP BY DATE_TRUNC('month', order_date), customer_id
),
ranked AS (
    SELECT 
        month,
        customer_id,
        monthly_total,
        ROW_NUMBER() OVER (PARTITION BY month ORDER BY monthly_total DESC) AS rank
    FROM monthly_sales
)
SELECT 
    TO_CHAR(month, 'YYYY-MM') AS month,
    customer_id,
    monthly_total,
    rank
FROM ranked
WHERE rank <= 10
ORDER BY month DESC, rank;

WITH monthly_sales AS (
    SELECT 
        DATE_FORMAT(order_date, '%Y-%m-01') AS month,
        customer_id,
        SUM(total) AS monthly_total
    FROM orders
    WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
      AND status = 'completed'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m-01'), customer_id
),
ranked AS (
    SELECT 
        month,
        customer_id,
        monthly_total,
        ROW_NUMBER() OVER (PARTITION BY month ORDER BY monthly_total DESC) AS `rank`
    FROM monthly_sales
)
SELECT 
    DATE_FORMAT(month, '%Y-%m') AS month,
    customer_id,
    monthly_total,
    `rank`
FROM ranked
WHERE `rank` <= 10
ORDER BY month DESC, `rank`;

WITH monthly_sales AS (
    SELECT 
        DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) AS month,
        customer_id,
        SUM(total) AS monthly_total
    FROM orders
    WHERE order_date >= DATEADD(year, -1, GETDATE())
      AND status = 'completed'
    GROUP BY DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1), customer_id
),
ranked AS (
    SELECT 
        month,
        customer_id,
        monthly_total,
        ROW_NUMBER() OVER (PARTITION BY month ORDER BY monthly_total DESC) AS [rank]
    FROM monthly_sales
)
SELECT 
    FORMAT(month, 'yyyy-MM') AS month,
    customer_id,
    monthly_total,
    [rank]
FROM ranked
WHERE [rank] <= 10
ORDER BY month DESC, [rank];

WITH monthly_sales AS (
    SELECT 
        TRUNC(order_date, 'MM') AS month,
        customer_id,
        SUM(total) AS monthly_total
    FROM orders
    WHERE order_date >= ADD_MONTHS(SYSDATE, -12)
      AND status = 'completed'
    GROUP BY TRUNC(order_date, 'MM'), customer_id
),
ranked AS (
    SELECT 
        month,
        customer_id,
        monthly_total,
        ROW_NUMBER() OVER (PARTITION BY month ORDER BY monthly_total DESC) AS rnk
    FROM monthly_sales
)
SELECT 
    TO_CHAR(month, 'YYYY-MM') AS month,
    customer_id,
    monthly_total,
    rnk
FROM ranked
WHERE rnk <= 10
ORDER BY month DESC, rnk;

INSERT INTO products (sku, name, price, updated_at)
VALUES ('ABC123', 'Widget', 29.99, CURRENT_TIMESTAMP)
ON CONFLICT (sku) 
DO UPDATE SET 
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    updated_at = CURRENT_TIMESTAMP
WHERE products.price != EXCLUDED.price;

INSERT INTO products (sku, name, price, updated_at)
VALUES ('ABC123', 'Widget', 29.99, CURRENT_TIMESTAMP)
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    price = VALUES(price),
    updated_at = CURRENT_TIMESTAMP;

MERGE INTO products AS target
USING (SELECT 'ABC123' AS sku, 'Widget' AS name, 29.99 AS price) AS source
ON target.sku = source.sku
WHEN MATCHED AND target.price != source.price THEN
    UPDATE SET 
        name = source.name,
        price = source.price,
        updated_at = GETDATE()
WHEN NOT MATCHED THEN
    INSERT (sku, name, price, updated_at)
    VALUES (source.sku, source.name, source.price, GETDATE());

MERGE INTO products target
USING (SELECT 'ABC123' AS sku, 'Widget' AS name, 29.99 AS price FROM dual) source
ON (target.sku = source.sku)
WHEN MATCHED THEN
    UPDATE SET 
        target.name = source.name,
        target.price = source.price,
        target.updated_at = SYSTIMESTAMP
    WHERE target.price != source.price
WHEN NOT MATCHED THEN
    INSERT (sku, name, price, updated_at)
    VALUES (source.sku, source.name, source.price, SYSTIMESTAMP);

SELECT 
    department,
    employee_name,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    salary::float / SUM(salary) OVER (PARTITION BY department) * 100 AS pct_of_dept,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg,
    STRING_AGG(employee_name, ', ' ORDER BY salary DESC) 
        OVER (PARTITION BY department) AS all_employees
FROM employees
ORDER BY department, salary DESC;

SELECT 
    department,
    employee_name,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    salary / SUM(salary) OVER (PARTITION BY department) * 100 AS pct_of_dept,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg,
    GROUP_CONCAT(employee_name ORDER BY salary DESC SEPARATOR ', ') 
        OVER (PARTITION BY department) AS all_employees
FROM employees
ORDER BY department, salary DESC;

SELECT 
    department,
    employee_name,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    CAST(salary AS float) / SUM(salary) OVER (PARTITION BY department) * 100 AS pct_of_dept,
    salary - AVG(CAST(salary AS float)) OVER (PARTITION BY department) AS diff_from_avg,
    STRING_AGG(employee_name, ', ') WITHIN GROUP (ORDER BY salary DESC)
        OVER (PARTITION BY department) AS all_employees
FROM employees
ORDER BY department, salary DESC;

SELECT 
    department,
    employee_name,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    salary / SUM(salary) OVER (PARTITION BY department) * 100 AS pct_of_dept,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg,
    LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY salary DESC)
        OVER (PARTITION BY department) AS all_employees
FROM employees
ORDER BY department, salary DESC;

WITH RECURSIVE org_tree AS (
    SELECT 
        id,
        name,
        manager_id,
        1 AS level,
        name::text AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        ot.level + 1,
        ot.path || ' > ' || e.name
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree
ORDER BY path;

WITH RECURSIVE org_tree AS (
    SELECT 
        id,
        name,
        manager_id,
        1 AS level,
        CAST(name AS CHAR(1000)) AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        ot.level + 1,
        CONCAT(ot.path, ' > ', e.name)
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree
ORDER BY path;

WITH org_tree AS (
    SELECT 
        id,
        name,
        manager_id,
        1 AS level,
        CAST(name AS VARCHAR(MAX)) AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        ot.level + 1,
        ot.path + ' > ' + e.name
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree
ORDER BY path
OPTION (MAXRECURSION 100);

WITH org_tree (id, name, manager_id, level, path) AS (
    SELECT 
        id,
        name,
        manager_id,
        1 AS level,
        name AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        ot.level + 1,
        ot.path || ' > ' || e.name
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree
ORDER BY path;

SELECT 
    id,
    data->>'name' AS name,
    data->'address'->>'city' AS city,
    (data->>'age')::int AS age,
    jsonb_array_length(data->'tags') AS tag_count
FROM users
WHERE data @> '{"status": "active"}'
  AND (data->>'age')::int >= 18;

SELECT 
    id,
    JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) AS name,
    JSON_UNQUOTE(JSON_EXTRACT(data, '$.address.city')) AS city,
    CAST(JSON_EXTRACT(data, '$.age') AS UNSIGNED) AS age,
    JSON_LENGTH(JSON_EXTRACT(data, '$.tags')) AS tag_count
FROM users
WHERE JSON_CONTAINS(data, '"active"', '$.status')
  AND CAST(JSON_EXTRACT(data, '$.age') AS UNSIGNED) >= 18;

SELECT 
    id,
    data->>'$.name' AS name,
    data->>'$.address.city' AS city,
    CAST(data->>'$.age' AS UNSIGNED) AS age,
    JSON_LENGTH(data->'$.tags') AS tag_count
FROM users
WHERE JSON_CONTAINS(data, '"active"', '$.status')
  AND CAST(data->>'$.age' AS UNSIGNED) >= 18;

SELECT 
    id,
    JSON_VALUE(data, '$.name') AS name,
    JSON_VALUE(data, '$.address.city') AS city,
    CAST(JSON_VALUE(data, '$.age') AS int) AS age,
    (SELECT COUNT(*) FROM OPENJSON(data, '$.tags')) AS tag_count
FROM users
WHERE JSON_VALUE(data, '$.status') = 'active'
  AND CAST(JSON_VALUE(data, '$.age') AS int) >= 18;

SELECT 
    DATE_TRUNC('week', created_at) AS week,
    COUNT(*) AS signups,
    COUNT(*) FILTER (WHERE status = 'active') AS active_signups
FROM users
WHERE created_at >= DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY DATE_TRUNC('week', created_at)
ORDER BY week;

SELECT 
    DATE(DATE_SUB(created_at, INTERVAL WEEKDAY(created_at) DAY)) AS week,
    COUNT(*) AS signups,
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_signups
FROM users
WHERE created_at >= MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL QUARTER(CURDATE())-1 QUARTER
GROUP BY DATE(DATE_SUB(created_at, INTERVAL WEEKDAY(created_at) DAY))
ORDER BY week;

SELECT 
    DATETRUNC(week, created_at) AS week,
    COUNT(*) AS signups,
    COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_signups
FROM users
WHERE created_at >= DATETRUNC(quarter, GETDATE())
GROUP BY DATETRUNC(week, created_at)
ORDER BY week;

SELECT 
    DATEADD(week, DATEDIFF(week, 0, created_at), 0) AS week,
    COUNT(*) AS signups,
    COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_signups
FROM users
WHERE created_at >= DATEADD(quarter, DATEDIFF(quarter, 0, GETDATE()), 0)
GROUP BY DATEADD(week, DATEDIFF(week, 0, created_at), 0)
ORDER BY week;

SELECT 
    TRUNC(created_at, 'IW') AS week,
    COUNT(*) AS signups,
    COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_signups
FROM users
WHERE created_at >= TRUNC(SYSDATE, 'Q')
GROUP BY TRUNC(created_at, 'IW')
ORDER BY week;

SELECT 
    id,
    title,
    ts_rank(search_vector, query) AS rank
FROM articles,
     to_tsquery('english', 'database & performance') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

SELECT 
    id,
    title,
    MATCH(title, content) AGAINST('database performance' IN NATURAL LANGUAGE MODE) AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST('database performance' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC
LIMIT 20;

SELECT TOP 20
    id,
    title,
    KEY_TBL.RANK AS rank
FROM articles
INNER JOIN CONTAINSTABLE(articles, (title, content), 'database AND performance') AS KEY_TBL
    ON articles.id = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;