Query with the window function FIRST_VALUE produces duplicates

Question

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?

Answer 1

The problem in question is that the added correlated subqueries make previously identical rows distinct. And DISTINCT is applied after computing the whole SELECT list. So you end up with "duplicates".

Move your original query into subquery, and compute the additional columns in a LATERAL subquery after applying DISTINCT:

SELECT *
FROM  (

   -- your original query here

   ) c
CROSS  JOIN LATERAL (
   SELECT count(id) AS sessions
        , sum(extract(epoch FROM end_time - start_time)) AS timer
   FROM   timelogs
   WHERE  challenge_id = c.id
   ) t;

Computing sessions and timer in a single subquery is cheaper.

About the sequence of events in a SELECT query:

• Best way to get result count before LIMIT was applied

About lateral subqueries:

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?