CODEWITHSUNDEEP
sqlsql-server
gal leshem
gal leshem

Reputation: 561

A query that calculates a percentage based on a previous date

I have a table of Daily information on player logins, with Date, ID, Sessions Columns.

I am trying to write query that calculate the percentage of players who did not return to the game for 14 days.

I secceded to write query of Daily distribution of the amount of players entering the game.

SELECT [Date], COUNT(ID) Daily_Active_Users
FROM Daily_Activity
GROUP BY [Date]
ORDER BY [Date]

enter image description here

How Can I check which do players return to the game on the 14 following day, and calculate the percentage?

The results I want:

    Date     | Percentage_players_not_return
2019-03-15   |         0.2305
2019-03-16   |         0.3416
2019-03-17   |         0.2725
2019-03-18   |         0.1335
2019-03-19   |         0.2145
2019-03-20   |         0.3456

Upvotes: 0

Views: 399

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Use window functions:

SELECT [Date],
       COUNT(*) as Daily_Active_Users,
       SUM(CASE WHEN next_date = DATEADD(day, 1, date) THEN 1 ELSE 0 END) as Active_Next_day
FROM (SELECT da.*,
             LEAD(date) OVER (PARTITION BY player ORDER BY date) as next_date
      FROM Daily_Activity da
     ) da
GROUP BY [Date]
ORDER BY [Date] ;

You can divide the two calculated values to get a ratio.

Upvotes: 0

dotnetom
dotnetom

Reputation: 24901

If you are using SQL Server 2012 or later you can use function LAG to get active users count from the previous day and use it for calculations:

;with DailyUsers as (
    SELECT [Date], COUNT(ID) Daily_Active_Users
    FROM Daily_Activity
    GROUP BY [Date]
  )
SELECT 
    d.[Date], 
    -- Function LAG is used to get active users from the previous day
    d.[Daily_Active_Users] / LAG (d.Daily_Active_Users) OVER (ORDER BY d.[Date] DESC) AS ReturningUsersPercentage
FROM DailyUsers

Upvotes: 1

forpas
forpas

Reputation: 164064

With 2 ctes which return the number of players for each day and the number of players that return each day from the previous day:

with 
  dailycounters as (
    select [Date], count(id) counter 
    from Daily_Activity 
    group by [Date]
  ),
  returncounters as (
    select 
      t.[Date], count(t.id) returncounter
    from Daily_Activity t 
    inner join Daily_Activity y
    on y.[Date] = dateadd(day, -1, t.[Date]) and y.id = t.id
    group by t.[Date]
  )
select 
  (100.0 * r.returncounter / d.counter) returnpercentage
from returncounters r inner join dailycounters d
on d.[Date] = dateadd(day, -1, r.[Date])

Upvotes: 2

Related Questions