Setup
My tables are like this:
CREATE TABLE challenges (
id uuid,
user_id uuid,
fe_challenge_id uuid,
status_type varchar(50),
created_at timestamp,
updated_at timestamp
);
This table stores users' attempted challenges. status_type
can be Ended
or Paused
. User can attempt any challenge more than once, but the condition is any new same challenge won't be logged before the previous one is ended.
CREATE TABLE timelogs (
id uuid,
challenge_id uuid,
start_time timestamp,
end_time timestamp,
created_at timestamp,
updated_at timestamp
);
This table stores the users' logs for their challenges, when user opens challenge screen, entry is made in this table with start_time
and when closes the screen, entry is updated with end_time
. This way we counts sessions and total time of a specific challenge attempted by user. The number of entries of logs as a session and all of their time is calculated via start and end time.
CREATE TABLE scripts (
id uuid,
user_id uuid,
challenge_id uuid,
script integer,
timestamp timestamp,
created_at timestamp,
updated_at timestamp
);
This table stores scripts read by a user in a challenge. i.e. in a challenge, there can be hundreds of scripts.
DB Fiddle with sample data.
Task
I want to fetch challenges counting its sessions and total time spent on a challenge by a user and with script no which is the first and last for a challenge.
The twist is, query must not return a challenge ended multiple i.e. if user has completed challenge A
5 times, and sixth one is in progress, then the query should return one log for Ended (the latest one) and one which is in progress.
Here is the query I'm using to fetch the records:
SELECT DISTINCT
FIRST_VALUE(c.id) OVER w id,
FIRST_VALUE(c.user_id) OVER w user_id,
FIRST_VALUE(c.fe_challenge_id) OVER w fe_challenge_id,
FIRST_VALUE(c.status_type) OVER w status_type,
FIRST_VALUE(c.created_at) OVER w created_at,
FIRST_VALUE(c.updated_at) OVER w updated_at,
FIRST_VALUE(r.script) OVER wr last_script,
FIRST_VALUE(r.script) OVER fr first_script
FROM
challenges as c,
scripts as r
WHERE
c.user_id='cc21564f-17ce-4d63-8447-ac3e2d69a3e4'
and c.status_type != 'Incomplete'
and c.status_type != 'Null'
and c.id = r.challenge_id
WINDOW w as (
PARTITION BY c.user_id, c.fe_challenge_id, c.status_type
ORDER BY c.updated_at desc
),
wr as (
PARTITION BY r.challenge_id
ORDER BY r."timestamp" DESC
),
fr as (
PARTITION BY r.challenge_id
ORDER BY r."timestamp" ASC
);
This query returns accurate data like in below image:
But when I try to fetch timestamp from script table or calculate the sessions or time from timelog table, it creates duplication. i.e.
SELECT DISTINCT
FIRST_VALUE(c.id) OVER w id,
FIRST_VALUE(c.user_id) OVER w user_id,
FIRST_VALUE(c.fe_challenge_id) OVER w fe_challenge_id,
FIRST_VALUE(c.status_type) OVER w status_type,
FIRST_VALUE(c.star_amount) OVER w star_amount,
FIRST_VALUE(c.created_at) OVER w created_at,
FIRST_VALUE(c.updated_at) OVER w updated_at,
FIRST_VALUE(r.script) OVER wr last_script,
FIRST_VALUE(r.script) OVER fr first_script,
FIRST_VALUE(r."timestamp") OVER wr last_script_time, ---This line
(SELECT count(id) from timelogs where challenge_id=c.id) as sessions, --- This line
(SELECT sum(extract(epoch from (end_time::timestamp - start_time::timestamp))) from timelogs where challenge_id=c.id) as timer ---This line
FROM
challenges as c,
scripts as r
WHERE
c.user_id='cc21564f-17ce-4d63-8447-ac3e2d69a3e4'
and c.status_type != 'Incomplete'
and c.status_type != 'Null'
and c.id = r.challenge_id
WINDOW w as (
PARTITION BY c.user_id, c.fe_challenge_id, c.status_type
ORDER BY c.updated_at desc
),
wr as (
PARTITION BY r.challenge_id
ORDER BY r."timestamp" DESC
),
fr as (
PARTITION BY r.challenge_id
ORDER BY r."timestamp" ASC
);
The result is below:
You will notice that the number of records is also increased from 29 to 34 and the two highlight records are duplicated with different values.
If I missed something, please ask for it.
Why do I get duplicates? Is there a better approach?
sessions
for eachid
? Your join byid
in thesessions
subquery is not on theFIRST_VALUE(id)
but rather it'll join to everyid
in the table instead. If you want it to join by theFIRST_VALUE(id)
then you'd need to wrap your entire query in a CTE or subquery, and then join to it to get thesessions
.