Monthly active users in ClickHouse

Question

Since ClickHouse doesn't support WITH clause for queries, I'm having troubles implementing a query for calculating MAU (Monthly active users calculated day by day and the 30 days range is moving day by day as well). In Postgres I would do this:

WITH days AS (
  SELECT created_at::DATE AS day,
  FROM events
  WHERE created_at > '2019-04-01'
  GROUP BY 1 
)
SELECT day,
         (SELECT count(distinct user_id)
          FROM events
          WHERE events.created_at::DATE BETWEEN days.day-29 AND days.day
            AND created_at > '2019-04-01'
         ) AS mau
FROM days

Desired result looks like this

┌────────day─┬──mau─┐
│ 2019-04-04 │ 1278 │
│ 2019-04-05 │ 1375 │
│ 2019-04-06 │ 1162 │
│ 2019-04-07 │ 1237 │
│ 2019-04-08 │ 1272 │
│ 2019-04-09 │ 1263 │
│ 2019-04-10 │ 1336 │
│ 2019-04-11 │ 1457 │
│ 2019-04-12 │ 1286 │
│ 2019-04-13 │ 1210 │
│ 2019-04-14 │ 1253 │
│ 2019-04-15 │ 2342 │
│ 2019-04-16 │ 1464 │
│ 2019-04-17 │ 1513 │
│ 2019-04-18 │ 1158 │
│ 2019-04-19 │ 1207 │
│ 2019-04-20 │ 1222 │
│ 2019-04-21 │ 1054 │
│ 2019-04-22 │ 1505 │
│ 2019-04-23 │ 5287 │
│ 2019-04-24 │ 4367 │
│ 2019-04-25 │ 3624 │
│ 2019-04-26 │ 2415 │
│ 2019-04-27 │ 1962 │
│ 2019-04-28 │ 2032 │
│ 2019-04-29 │ 2547 │
│ 2019-04-30 │ 4059 │
└────────────┴──────┘

Answer 1

You could try something like this:

SELECT
    created_at,
    uniqExact(user_id) AS mau
FROM
(
    SELECT
        created_at + n AS created_at,
        user_id
    FROM
    (
        SELECT
            today() - 14 AS created_at,
            123 AS user_id
        UNION ALL
        SELECT
            today() - 20 AS created_at,
            456 AS user_id
    )
    ARRAY JOIN range(30) AS n
)
WHERE created_at <= today()
GROUP BY created_at
FORMAT TSV

2019-04-11  1
2019-04-12  1
2019-04-13  1
2019-04-14  1
2019-04-15  1
2019-04-16  1
2019-04-17  2
2019-04-18  2
2019-04-19  2
2019-04-20  2
2019-04-21  2
2019-04-22  2
2019-04-23  2
2019-04-24  2
2019-04-25  2
2019-04-26  2
2019-04-27  2
2019-04-28  2
2019-04-29  2
2019-04-30  2
2019-05-01  2

21 rows in set. Elapsed: 0.005 sec.