Hello I have troubles with my data filter on a particular query :
the error message I get is : "2024-13 is an unvalid date'
here is the code :
WITH telechargement AS (
SELECT
COUNT(DISTINCT id) AS tel,
-- COUNT(DISTINCT CONCAT(user_id, '-', uwcaseid)) AS tel,
CONCAT(DATE_PART('year', date), '-', DATE_PART('week', date)) AS year_week
FROM events_with_users_info
WHERE {{env}} AND {{type_evenement}} --AND {{date}}
AND event_type in ('generate-uwcase-pdf') and event_category = 'UWA'
AND actif_attendu = 'Actif Attendu'
and env not in ('demo','master','beta')
GROUP BY year_week
),
recherche AS (
SELECT
COUNT(DISTINCT (CASE WHEN event_type in ('create-uwcase', 'update-building-facet') THEN uwcaseid ELSE id END)) AS rech,
CONCAT(DATE_PART('year', date), '-', DATE_PART('week', date)) AS year_week
FROM events_with_users_info
WHERE {{env}} AND {{type_evenement}} --AND {{date}}
AND event_type in ('fetch-uwcase')
AND actif_attendu = 'Actif Attendu' and event_category = 'UWA'
and env not in ('demo','master','beta')
GROUP BY year_week
),
creation AS (
SELECT
COUNT(DISTINCT (CASE WHEN event_type in ('create-uwcase', 'update-building-facet') THEN uwcaseid ELSE id END)) AS crea,
CONCAT(DATE_PART('year', date), '-', DATE_PART('week', date)) AS year_week
FROM events_with_users_info
WHERE {{env}} AND {{type_evenement}} --AND {{date}}
AND event_type in ('create-uwcase')
AND actif_attendu = 'Actif Attendu' and event_category = 'UWA'
and env not in ('demo','master','beta')
GROUP BY year_week
),
semaine AS (
SELECT DISTINCT CONCAT(DATE_PART('year', date), '-', DATE_PART('week', date)) AS year_week,
DATE_PART('week', date) AS week_nb,
DATE_PART('year', date) AS event_year
FROM events_with_users_info
where {{date}} and date(date) > '2022-10-09'
)
SELECT semaine.year_week, COALESCE(creation.crea, 0) + COALESCE(recherche.rech, 0) as rech,
CASE
WHEN COALESCE(creation.crea, 0) + COALESCE(recherche.rech, 0) <> 0 THEN
(1.00 * COALESCE(telechargement.tel, 0)) / (COALESCE(creation.crea, 0) + COALESCE(recherche.rech, 0))
ELSE 0
END AS pourcentage
FROM semaine
left join telechargement on semaine.year_week = telechargement.year_week
left join recherche on semaine.year_week = recherche.year_week
left join creation on semaine.year_week = creation.year_week
GROUP BY semaine.year_week, telechargement.tel, creation.crea, recherche.rech, semaine.event_year, semaine.week_nb
ORDER BY semaine.event_year, semaine.week_nb
and when I put no date filter or a date filter without the week 13, it works fine :
