title: DAU/MAU calculation for segments
body: <p>I have written the following query to calculate DAU/MAU ratio:</p>
  
  <pre><code>WITH dau AS
  (
    SELECT TRUNC(created_at) AS created_at,
           CASE
             WHEN user_agent SIMILAR TO '%(Mobile|iPhone|iPod|iPad|Android)%' THEN 'non-desktop'
             ELSE 'desktop'
           END AS trafficsource,
           COUNT(DISTINCT member_id) AS dau
    FROM table ds
    WHERE ds.created_at BETWEEN '2017-01-01' AND '2017-12-11'
    AND   member_id &lt;&gt; 2
    AND   member_id NOT IN (SELECT memberid FROM auth2.membersinglerole WHERE roleid = 25)
    GROUP BY TRUNC(created_at),
             trafficsource
  )
  SELECT created_at,
         trafficsource,
         dau,
         (SELECT COUNT(DISTINCT member_id)
          FROM table ds
          WHERE member_id &lt;&gt; 2
          AND   member_id NOT IN (SELECT memberid FROM auth2.membersinglerole WHERE roleid = 25)
          AND   ds.created_at BETWEEN dau.created_at - 29*INTERVAL '1 day' AND dau.created_at) AS mau,
         (dau / CAST(mau AS float)) AS "DAU/MAU",
         (dau / CAST(mau AS float))*30 AS DaysOutOf30
  FROM dau
  WHERE EXTRACT(dayofweek FROM created_at) NOT IN (0,6)
  AND   EXTRACT(month FROM created_at) NOT IN (5,6,7)
  ORDER BY created_at
  </code></pre>
  
  <p>This query creates 2 segments for 'desktop' and 'non-desktop'. But notably, the query returns the same 'MAU' numbers for both these segments for the same day, as follows..</p>
  
  <pre><code>created_at    trafficsource    dau    mau    DAU/MAU    DaysOutOf30
  2017-01-02  desktop 4157    140834  0.02951702003777497 0.8855106011332491
  2017-01-02  non-desktop 801 140834  0.005687547041197438    0.17062641123592315
  2017-01-03  desktop 12610   140468  0.089771335820258   2.6931400746077396
  2017-01-03  non-desktop 2891    140468  0.020581199988609505    0.6174359996582851
  2017-01-04  non-desktop 4033    137516  0.029327496436778264    0.8798248931033479
  2017-01-04  desktop 17902   137516  0.1301812152767678  3.9054364583030337
  </code></pre>
  
  <p>How do I fix the query to return 'MAU' values for the segments created? Any help would be highly appreciated.</p>