Gamers and Logins

     

Write an SQL query to fetch the fraction of the gamers that logged in for at least 2 consecutive days from their first login date. Fraction :- (Total number of players that logged in for 2 days continuously) / (Total number of players)

Input :-

Gamers table :-
(Gamer_ID, Login_Date) is the Primary Key column for the Gamers table.

Gamer_ID	Login_Date
1	2018-07-04
3	2016-05-12
1	2018-07-05
2	2010-02-01
3	2017-09-10

Output :-

Fraction

0.33

Query :-

select 
round(count(distinct g.Gamer_ID)/(select count(distinct Gamer_ID) from Gamers),2) Fraction
from
(select Gamer_ID, min(Login_Date) first_date 
from Gamers
group by Gamer_ID) g
join Gamers g1 on g.first_date+1=g1.Login_Date
and g.Gamer_ID=g1.Gamer_ID;

Explanation : - 

The given query calculates a fraction representing the proportion of gamers who logged in on consecutive days out of the total number of distinct gamers. Let's break down the query step by step:

1)Subquery g :- 

• This subquery selects the Gamer_ID and the minimum Login_Date for each gamer from the Activity table. 
• It groups the records by Gamer_ID to ensure we get only one record per gamer. 
• The result is a temporary table g that contains the Gamer_ID and the earliest Login_Date for each gamer.

2)Joining with Gamers table :- 

• The subquery g is joined with the Gamers table using a join condition that checks if the first_date from g (the earliest login date) incremented by 1 day matches the Login_Date in the Gamers g1 table.
• Additionally, the join condition also ensures that the Gamer_ID matches between the subquery g and the Gamers table.
• This join filters out the gamers who did not log in on consecutive days.

3)Counting distinct gamers :-

• The query counts the number of distinct Gamer_ID values from the result of the join operation, representing the count of gamers who logged in on consecutive days.

4)Calculating the fraction:

• The count of distinct gamers who logged in on consecutive days is divided by the count of distinct Gamer_ID values from the Gamers table.
• The round function is used to round the result to 2 decimal places.
• The resulting value represents the fraction or proportion of gamers who logged in on consecutive days out of the total number of distinct gamers.

In summary, this query calculates the proportion of gamers who logged in on consecutive days by finding the earliest login date for each gamer, joining it with the Gamers table to filter out non-consecutive logins, and then dividing the count of consecutive gamers by the total number of distinct gamers.