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
- Rolling 7 Day Distinct Count of Active Users
- Get count of active users for each day
- get All distinct (by ID) Users list and count only thoose who is Active
- How to add rolling 7 and 30 day columns to my daily count of distinct logins in SQL Server
- Count Number of Repeat Callers in a rolling 7 day peroid
- How to count the number of distinct users that meet a certain condition using CASE WHEN
- How to use the distinct and count function to get distinct users and number of messages sent
- How to calculate a count of users who did a thing X times during a rolling 7-day period in SQL Server?
- Use OVER or LAG functions to COUNT the number of orders in a ROLLING five day period?
- Distinct count of users grouped by a column and distributed across bins
- SQL Query for 7 Day Rolling Average in SQL Server
- count distinct records (all columns) not working
- SSRS Count Distinct Fields!B.value Where Fields!A.value =
- Count of Distinct Rows Without Using Subquery
- SQL Server Server query - Count distinct DateTime field
- Performance using DISTINCT COUNT
- How to get distinct count of values of all the columns of a table based on where condition in sql server?
- Fastest Way to Count Distinct Values in a Column, Including NULL Values
- How do I count occurrences by day in SQL?
- Count on DISTINCT of several fields work only on MySQL?
- SQL: Count distinct values from one column based on multiple criteria in other columns
- Count distinct and Null value is eliminated by an aggregate
- Count Number of Distinct Columns in a Row with tSQL
- Count status Id for each day
- How to add column which count distinct person any city
- Count number of times 2 distinct values appear in more than 1 row in SQL Table
- How to simulate count distinct in an indexed view?
- SQL Sever Getting Distinct Count using "Group By ... With Cube"
- Query for the average call length for all users for a day
- Count distinct one column based on criteria on another few columns
More questions with similar tag
- Add more than one filter to a query using same column
- CakePHP: error when trying to use mssql datasource
- How do I do a multiple insert?
- How to use Mirth to insert data in SQL Server database from xml file
- API: How can I remotely check if two data sets are identical, without sending all data across the API?
- SQL Server adding a Doc Breaks of paths to single delimited line
- How to enable the login for the default windows authentication? error:18470
- Like take long time on inner join
- Store query results or use nested queries
- Why am I seeing values of '2432-82-75 50:08:01' in Oracle DATE column?
- Variable expression vs Expression Task
- pyodbc error when connecting to SQL database "Cannot open database "database3" requested by the login."
- Write a query that returns the dependencies of an object
- How can I combine multiple rows into a comma-delimited list in SQL Server 2005?
- why is power(2.0, 1/2) = 1.0?
- SCOPE_IDENTITY in C# - range
- Query database tables for missing link
- Can't get my stored procedure IF statement working
- Select columns affect execution plan?
- XML Help in TSQL
- Error in converting Varchar into Datetime for calculating DateDiff in SQL Server
- How can I make the code inside a WHILE LOOP in SQL execute only once per LOOP?
- Returning variables from search to use in select statement
- Incorrect syntax near 'BEGIN'. in TSQL
- How to select at most N records for a given value of a column in a table?
- Not able to get the stored procedure's result in variable?
- Ordering results in SQL select query
- Data Multiplication inside a SQL Server stored procedure
- Slow query performance with Partitioned tables?
- SQL Server LocalDb ProjectsV13 Connection Failure
Source:
stackoverflow.com