Problem with Leetcode SQL task - Game Play Analysis IV

Question

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.

You can't really expect us to comment without knowing what the task is, and you can't really expect us to click over to Leetcode to look it up. — Tim Roberts
– Tim Roberts, Commented Sep 4, 2024 at 16:58

Tim Roberts · Accepted Answer · 2024-09-04 17:15:03Z

1

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.

answered Sep 4, 2024 at 17:15

Tim Roberts

55.3k4 gold badges29 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

qanqanqan Over a year ago

Didn't know that in PostgreSQL integer division truncates result towards zero / returns lower ceiled integer. Compared this operation in MySQL and PostgreSQL and now i can see the difference - MySQL query results in 0.(33), when in PostgreSQL it is zero... Thanks a lot!

Frank Heikens Over a year ago

It's in the manual and returns a BIGINT: count ( * ) → bigint. postgresql.org/docs/current/functions-aggregate.html MySQL behavior depends on the sql_mode used, see dev.mysql.com/doc/refman/9.0/en/…

Zegarek Over a year ago

I wouldn't use numeric and float interchangeably - in Postgres these are reserved keywords for two distinct data types that don't work the same. The rule is "operator returns the same data type as its argument(s). Calls involving multiple argument data types, such as integer + numeric, are resolved by using the type appearing later in these lists.", the list being "smallint, integer, bigint, numeric, real, and double precision". For literals, a dot is enough: select 2. / 3 ;

Collectives™ on Stack Overflow

Problem with Leetcode SQL task - Game Play Analysis IV

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related