CODEWITHSUNDEEP
sqlpostgresql
user3566351
user3566351

Reputation: 23

SQL - Subtracting dates in two consecutive records, then finding the average

I've been stuck on this for such a long time.
Let's say you have a table named Stays that shows the dates each person checked-in and checked-out of a specific hotel: 

+---------+------------+------------+
| stay_id | event_type |    date    |
+---------+------------+------------+
|     156 | check-in   | 2019-03-21 |
|     156 | check-out  | 2019-03-23 |
|     157 | check-in   | 2019-03-23 |
|     157 | check-out  | 2019-03-24 |
|     158 | check-in   | 2019-03-25 |
|     158 | check-out  | 2019-03-28 |
+---------+------------+------------+


With the assumption that they're only two records for each stay (check-in and checkout), and the check-out date is always after the check-in date.
How do you find out the average number of nights a person stays in this hotel (aka average of the number of nights between the check-in date and check-out date)? In pseudo-code, I know for each stay_id, I have to use DATEDIFF to subtract the checkout date - the check in date, then get the average of all of the results, and perhaps use the LAG window function? I put the table in SQLFiddle and I'm stuck. The expected result would be:

+-----+
| AVG |
+-----+
|   2 |
+-----+

Upvotes: 1

Views: 72

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13006

Here's your query. You just need left join the check-out type

select avg(date_part('day', t2.dated::timestamp - t1.dated::timestamp)) from test t1
left join test t2 on t2.event_type = 'check-out' and t1.stay_id = t2.stay_id
where t1.event_type = 'check-in'

see dbfiddle

Upvotes: 1

Related Questions