Queries

gistfile1.txt

-- Counting unique users who have atleast produced one event
SELECT 
    "system", 
    COUNT(DISTINCT user_id) AS nof_users
FROM "catalog-s3".bronze.mongodb.archiver.events
WHERE
"timestamp" >= '2024-08-01' AND
"timestamp" < '2025-08-01'
GROUP BY "system"
ORDER BY "system"
;

-- Counting unique students who have atleast finished one exerciseSeries
SELECT
    sid,
    COUNT(DISTINCT user_bid) AS nof_users
FROM "catalog-s3".silver."fact_exercise_series_finished"
WHERE
  "ended_date_local" >= '2024-08-01' and
  "ended_date_local" < '2025-08-01'
GROUP BY sid
ORDER BY sid
;