Sum Register With Date SQL

Question

I'm trying to create this query at work, but when I run it it shows the following error, basically it adds up if the record is in that period of the week:

Syntax error at or near '"2022-05-17"'.(line 26, pos 100)

WITH weekly_tickets AS (
    SELECT 
        product AS produto,
        category AS categoria,
        reason AS motivo,
        DATE_TRUNC('week', created_date) AS semana,
        COUNT(daily_ticket) AS qtde_registros
    FROM tb_atendimento
    WHERE created_date >= DATE '2022-05-17' AND created_date <= DATE '2022-06-16'
    GROUP BY product, category, reason, semana
),
ranked_products AS (
    SELECT 
        produto,
        categoria,
        motivo,
        semana,
        qtde_registros,
        ROW_NUMBER() OVER (PARTITION BY produto ORDER BY qtde_registros DESC) AS rank
    FROM weekly_tickets
)
SELECT 
    produto,
    categoria,
    motivo,
    SUM(CASE WHEN semana = DATE '2022-05-17' THEN qtde_registros ELSE 0 END) AS "2022-05-17",
    SUM(CASE WHEN semana = DATE '2022-05-24' THEN qtde_registros ELSE 0 END) AS "2022-05-24",
    SUM(CASE WHEN semana = DATE '2022-05-31' THEN qtde_registros ELSE 0 END) AS "2022-05-31",
    SUM(CASE WHEN semana = DATE '2022-06-07' THEN qtde_registros ELSE 0 END) AS "2022-06-07"
FROM ranked_products
WHERE rank <= 5
GROUP BY produto, categoria, motivo
ORDER BY produto;

Expect something like this:

Produto	categoria	motivo	2022-05-17	2022-05-24	2022-05-31	2022-06-07
First	row	row	50	100	30	76
Second	row	row	70	200	40	80

Answer 1

try:

WITH weekly_tickets AS (
    SELECT 
        product AS produto,
        category AS categoria,
        reason AS motivo,
        DATE_TRUNC('week', created_date) AS semana,
        COUNT(*) AS qtde_registros
    FROM tb_atendimento
    WHERE created_date >= DATE '2022-05-17' AND created_date <= DATE '2022-06-16'
    GROUP BY product, category, reason, semana
),
ranked_products AS (
    SELECT 
        produto,
        categoria,
        motivo,
        semana,
        qtde_registros,
        ROW_NUMBER() OVER (PARTITION BY produto ORDER BY qtde_registros DESC) AS rank
    FROM weekly_tickets
)
SELECT 
    produto,
    categoria,
    motivo,
    SUM(CASE WHEN semana = DATE '2022-05-16' THEN qtde_registros ELSE 0 END) AS week_2022_05_16,
    SUM(CASE WHEN semana = DATE '2022-05-23' THEN qtde_registros ELSE 0 END) AS week_2022_05_23,
    SUM(CASE WHEN semana = DATE '2022-05-30' THEN qtde_registros ELSE 0 END) AS week_2022_05_30,
    SUM(CASE WHEN semana = DATE '2022-06-06' THEN qtde_registros ELSE 0 END) AS week_2022_06_06,
    SUM(CASE WHEN semana = DATE '2022-06-13' THEN qtde_registros ELSE 0 END) AS week_2022_06_13
FROM ranked_products
WHERE rank <= 5
GROUP BY produto, categoria, motivo
ORDER BY produto;