title: How to get the monthly 7-day active users?
body: <p>In my database I have two fields that are used to identify a user, <code>timestamp</code> and <code>instance_id</code>. I want to be able to get the monthly 7-day active users from this data. I have tried the following query but it just returns the same timestamp and 1 for every row. </p>
  
  <pre><code>SELECT 
      FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_MICROS(date)) as target,
      SUM(CASE WHEN period = 7  THEN users END) as days_07
      # SUM(CASE WHEN period = 14 THEN users END) as days_14,
      # SUM(CASE WHEN period = 30 THEN users END) as days_30
  FROM (
      SELECT 
          activity.date as date,
          periods.period as period,
          COUNT(DISTINCT user) as users
      FROM (
          SELECT
              event.timestamp_micros as date, 
              user_dim.app_info.app_instance_id as user
          FROM `hidden.*` 
          CROSS JOIN 
              UNNEST(event_dim) as event  
      ) as activity
      CROSS JOIN (
          SELECT 
              event.timestamp_micros  as date
          FROM `hidden.*` 
          CROSS JOIN 
              UNNEST(event_dim) as event 
          GROUP BY event.timestamp_micros
      ) as dates
      CROSS JOIN (
          SELECT period 
          FROM 
              (
                  SELECT 7 as period 
                  # UNION ALL 
                  # SELECT 14 as period 
                  # UNION ALL
                  # SELECT 30 as period
              )
      ) as periods
      WHERE 
          dates.date &gt;= activity.date 
      AND 
          SAFE_CAST(FLOOR(TIMESTAMP_DIFF(TIMESTAMP_MICROS(dates.date), TIMESTAMP_MICROS(activity.date), DAY)/periods.period) AS INT64) = 0
      GROUP BY 1,2
  )
  GROUP BY date
  ORDER BY date DESC
  </code></pre>
  
  <p>I'm not too sure where to go from here and it's quite challenging to me because I'm not the best with SQL. Any assistance at all would be great. Thanks!</p>
  
  <p>I should also mention that these queries are going to be run within BigQuery and the data is being exported to BigQuery from Firebase. </p>