I’m trying to solve Leetcode SQL 50 task called “Game play analysis IV”:

Table: Activity

• player_id – int,
• device_id – int,
• event_date – date,
• games_played – int

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.

Write a solution 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.

Here’s a solution which i figured out in PostrgeSQL:

select
    round(count(player_id) / (select count(distinct player_id) from Activity), 2) as fraction
from Activity
where (player_id, event_date - interval '1 day') in (
    select player_id, min(event_date) from Activity
    group by player_id
)

But it won’t pass through – fraction keeps to be equal to zero in every possible test case.

However, if i add ‘::numeric’ to the division parts inside round() function,

round(count(player_id)::numeric / (select count(distinct player_id) from Activity)::numeric, 2)

it magically starts working.

Question: what’s wrong with my solution and why ‘::numeric’ fixes it?

PS: I know how to solve it using a CTE, but I still want to understand what’s the problem with the solution above.