score:1

The second join to transactions seems entirely unnecessary. You can do the counts using conditional aggregation. So, this version might be faster:

SELECT d2.date_tms,
       count(distinct case when d2.day_offset_nbr = dd.day_offset_nbr then t.user_id end) as Active_Members_Today,
       count(distinct t.user_id) as Rolling_7Day_Active_Members
FROM transactions t JOIN
     DATE_DIMENSION dd
     ON t.transaction_processed_date_id = dd.date_id JOIN
     DATE_DIMENSION d2
     ON d2.day_offset_nbr BETWEEN dd.day_offset_nbr AND (dd.day_offset_nbr + 6) AND 
WHERE t.user_initiated_ind = 'Y' AND -- Only User-initiated Transactions
      t.transaction_state_name = 'Accepted' AND -- Accepted Transactions Only
      dd.day_offset_nbr >= -731
GROUP BY d2.date_tms;

More questions

More questions with similar tag