27 lines
963 B
SQL
27 lines
963 B
SQL
SELECT
|
|
CONCAT(
|
|
EXTRACT(YEAR FROM w.created_at),
|
|
'-',
|
|
EXTRACT(WEEK FROM w.created_at)
|
|
) as week,
|
|
COUNT(DISTINCT s.distinct_id)
|
|
FROM
|
|
website_event AS w
|
|
JOIN
|
|
session as s ON s.session_id = w.session_id
|
|
WHERE
|
|
w.website_id = '16e7d807-4db5-45fd-92a9-27393445a153'
|
|
AND w.event_type = 1
|
|
AND s.distinct_id IS NOT NULL
|
|
AND substring(s.distinct_id SIMILAR '%#"@%#"' ESCAPE '#') <> '@oceanbox.io'
|
|
AND 2025 < EXTRACT(YEAR FROM w.created_at)
|
|
-- NOTE(simkir): We started with umami at this time, so the data that week was not complete
|
|
AND (NOT (EXTRACT(YEAR FROM w.created_at) = 2025 AND EXTRACT(WEEK FROM w.created_at) = 37))
|
|
AND (NOT (EXTRACT(YEAR FROM w.created_at) = 2025 AND EXTRACT(WEEK FROM w.created_at) = 1))
|
|
GROUP BY
|
|
week
|
|
ORDER BY
|
|
split_part(CONCAT(EXTRACT(YEAR FROM w.created_at), '-', EXTRACT(WEEK FROM w.created_at)), '-', 1)::int,
|
|
split_part(CONCAT(EXTRACT(YEAR FROM w.created_at), '-', EXTRACT(WEEK FROM w.created_at)), '-', 2)::int
|
|
;
|