1

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:

enter image description here

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:

enter image description here

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?

3
  • What do you expect the end result to look like when there are a different number of sessions for each id? Your join by id in the sessions subquery is not on the FIRST_VALUE(id) but rather it'll join to every id in the table instead. If you want it to join by the FIRST_VALUE(id) then you'd need to wrap your entire query in a CTE or subquery, and then join to it to get the sessions.
    – J.D.
    Jun 22, 2022 at 12:11
  • How do i do that, sorry I could not get you. @J.D.
    – mrabro
    Jun 22, 2022 at 14:17
  • "What do you expect the end result to look like?..." - Please update your post with an example and I'll be able to answer your question.
    – J.D.
    Jun 22, 2022 at 16:57

1 Answer 1

3

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:

About lateral subqueries:

4
  • Your whole query is hugely inefficient to begin with. Cleaning up is a job for a paid contractor, not fit for a question here. Jun 23, 2022 at 3:17
  • Thanks... I got the results by modifying query like yours, removing timelogs subquery from main, But it is still duplicating the records.. if the timestamp column from scripts is removed from the main query, then all the records are exact and result is fine. if you can look at the fiddle I shared... and try runing your query, you will get the idea of duplicating records. And I do know that my query is not that much efficient, I am trying on it. Thanks.
    – mrabro
    Jun 23, 2022 at 6:37
  • @RafiAbro: The same issue applies to the timestamp column from scripts Adding it to the row makes previously identical rows distinct. Define which timestamp to add per group (first, last?) and add only that (later). Jun 25, 2022 at 1:02
  • the last one, as I am trying to fetch last script number with it's timestamp.
    – mrabro
    Jun 27, 2022 at 5:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.