sqlpostgresqlnumeric

Problem with Leetcode SQL task - Game Play Analysis IV

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.

Solution

  • Without seeing the data I can't be certain, by the problem here is almost certainly that the COUNT function returns an integer, so you are doing an integer division, which truncates the result. Casting to NUMERIC makes them floating point, so you do a floating division.

    If you do

    SELECT 2 / 3;

    the result is zero. If you do

    SELECT 2::NUMERIC / 3::NUMERIC;

    the result is 0.66666666666666666667.