SQL Group by joining with time difference

Richard Bidin 0
Viewed 31

I have this college project with a good focus on the frontend, but I'm struggling with a SQL query (PostgreSQL) that needs to be executed at one of the backend endpoints.

The table I'm speaking of is the following:

id todo_id column_id time_in_status
0 259190 3 0
1 259190 10300 30
2 259190 10001 60
3 259190 10600 90
4 259190 6 30

A good way to simplify what it is, is saying it's a to-do organizer by vertical columns where each column would be represented by its column_id, and each row is task column change event.

With all that said what I need to get the job done is to generate a view (or another suggested better way) from this table that will show how long each task spent on each column_id. Also for a certain todo_id, column_id is not unique, so that could be multiple events on column 10300 and the table below would group by it and sum them

For example, the table above would output a view like this:

id todo_id time_in_column_3 time_in_column_10300 time_in_column_10001 ...
0 259190 0 30 60 ...
data-science sql postgresql
1 Answers
select *
from   crosstab(
               'select todo_id, id, time_in_status
                from t'
               )
as t(todo_id int, "time_in_column_3" int, "time_in_column_10300" int, "time_in_column_10001" int, "time_in_column_10600" int, "time_in_column_6" int )
todo_id time_in_column_3 time_in_column_10300 time_in_column_10001 time_in_column_10600 time_in_column_6
259190 0 30 60 90 30

Fiddle

DannySlor0
Related