Fix The Mau Problem While Calculating Dau And Mau On Amazon Redshift

I am using the following query to calculate MAU and DAU, according to this post: WITH dau AS ( SELECT TRUNC(created_at) AS created_at, COUNT(DISTINCT member_id) AS dau

Solution 1:

You should prefix column names:

WITH dau AS
(
  SELECT TRUNC(created_at) AS created_at,
         COUNT(DISTINCT member_id) AS dau
  FROMtable ds
  WHERE ds.created_at BETWEEN'2018-09-03'AND'2018-09-08'GROUPBY TRUNC(created_at)
)
SELECT created_at,
       dau,
       (SELECTCOUNT(DISTINCT member_id)
        FROMtable ds
        WHERE ds.created_at 
          BETWEEN dau.created_at -29*INTERVAL'1 day'AND dau.created_at) AS mau
          -- hereFROM dau
ORDERBY created_at

or:

SELECT TRUNC(created_at) AS created_at,
     COUNT(DISTINCT member_id) AS dau,
     COUNT(DISTINCT member_id) 
     FILTER(WHERE TRUNC(created_at)>=TRUNC(created_at)-29*INTERVAL'1 day') AS mau
FROMtable ds
WHERE ds.created_at BETWEEN'2018-09-03'AND'2018-09-08'GROUPBY TRUNC(created_at)
ORDERBY created_at