#LC 550. Game Play Analysis IV

(player_id, event_date) is the primary key of this table. This table shows the activity of players of some games. Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on someday using some device.

Request: Write an SQL query to report the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places. In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players.

Below is my code:

with cte1 as (
    select a1.player_id as player_id
    from activity a1
    right join activity a2
    on dateadd(day, 1, a1.event_date) = a2.event_date
)


select round(count(distinct cte1.player_id)/count(distinct activity.player_id), 2) as fraction
from activity, cte1

The result should be 0.33 but I got 0. Probably it's because select from two tables (which can work individually). Appreciate it if someone could help me understand why it's wrong. Thank you so much!

CodePudding user response：

multiplying it by 1.0 will auto cast the count() (int) as decimal.

select round(count(distinct cte1.player_id) * 1.0/count(distinct activity.player_id) * 1.0, 2) as fraction
from activity, cte1

CodePudding user response：

You're actually not too far off with your attempt.

Your DBMS might do integer division when all operands of a division are integer. Upcast at least one of the operands, for example by multiplying with 1.0.

But additionally try a left join, that's usually easier to understand and write. You only need one join whereas you join in the CTE and in the outer SELECT.

It's also recommended to always use explicit JOIN syntax instead of commas in the FROM clause. Explicit JOIN syntax is usually easier to understand and write without errors.

SELECT round(count(DISTINCT a2.player_id) * 1.0
             /
             count(DISTINCT a1.player_id) * 1.0,
             2) AS fraction
       FROM activity a1
            LEFT JOIN activity a2
                      ON a1.player_id = a2.player_id
                         AND a1.event_date = dateadd(day, -1, a2.event_date);

db<>fiddle (assuming SQL Server)