Stumped with average daily count per quarter

Transact-SQL
1

Hi all,

I'm really hoping someone can help me. I'm using Microsoft SQL Server Management Studio to do this.

I have a table with an ID, start_date, and end_date. Example would be:

123456789 | 6/1/16 | 9/30/16

The date range is the period of time the ID is active. In this example we would say that ID 123456789 has been active from 6/1/16 through 9/30/16. This table has many thousands of records. The ID is not a key.

I have a need to know what was the average distinct count of daily active users per quarter. Any suggestions?

2

Using a calendar table,

;WITH cte AS
(
	SELECT 
		Date,
		COUNT(DISTINCT Id) DailyIds
	FROM
		YourTable y
		INNER JOIN dbo.Dates d ON
			d.Date >= start_date
			AND d.Date <= end_date
	GROUP BY
		Date
)
SELECT 
	DATEADD(qq,DATEDIFF(qq,0,Date),0) AS QuarterStartDate,
	AVG(DailyIds) AS AverageUserCount
FROM
	cte
GROUP BY
	DATEADD(qq,DATEDIFF(qq,0,Date),0);

If you don't already have a calendar table (dbo.Dates in my example above), it is easy to create one; you will find many ways to do it if you search the web.

1 Like