Query needed to show data in a different way

user_13691114 0
Viewed 89

After making a query, I get this data in this format:

Start data

I need to set up a query that pairs dates related to same id and counts the difference in days. the result should be like this:

Desired output

I'm using postgresql. Could you please help me to set up the query to get the desired output ? Thanks in advance

postgresql sql
2 Answers

Hmmm . . . I'm thinking lead() and some additional filtering and arithmetic:

select id, date as date_start, next_date as date_end,
       (next_date - date_start) as days
from (select t.*, lead(date) over (partition by id order by date) as next_date
      from t
     ) t
where event = 0;
user_11440350

The most robust and maintainable answer is that by @GordonLinoff.
Another method to solve this can be using CTEs. Here I am assuming that event = 0 and event = 1 are paired (which is true in the example shown):

WITH t1 AS
  (SELECT id, date
   FROM t
   WHERE event = 0 rank() OVER (
                                ORDER BY id, date) AS id_date_rank),
     t2 AS
  (SELECT id, date
   FROM t
   WHERE event = 1 rank() OVER (
                                ORDER BY id, date) AS id_date_rank),
SELECT t1.id,
       t1.date AS date_start,
       t2.date AS date_end,
       DATE_PART('day', (t2.date - t1.date)) AS no_days
FROM t1
INNER JOIN t2 ON (t1.id_date_rank = t2.id_date_rank)
ORDER BY t1.id,
         t1.date;
user_9676210
Related