Marecky
Reputation: 2106
How to select rows displaying number of days since last moment of occurrence of specific predicate?
I have a table with data that look like this:
KEYWORD, CONVERSIONS, CREATED_AT
'Travel agent', 0, '2015-04-01'
'Travel agent', 0, '2015-04-02'
'Travel agent', 0, '2015-04-03'
'Travel agent', 0, '2015-04-04'
'Travel agent', 0, '2015-04-05'
'Travel agent', 0, '2015-04-06'
'Travel agent', 0, '2015-04-07'
'Travel agent', 0, '2015-04-08'
'Travel agent', 0, '2015-04-09'
'Travel agency vizier', 0, '2015-04-01'
'Travel agency vizier', 0, '2015-04-02'
'Travel agency vizier', 0, '2015-04-03'
'Travel agency vizier', 0, '2015-04-04'
'Travel agency vizier', 0, '2015-04-05'
'Travel agency vizier', 0, '2015-04-06'
'Travel agency vizier', 0, '2015-04-07'
'Travel agency vizier', 0, '2015-04-08'
'Travel agency vizier', 1, '2015-04-09'
'Coral', 0, '2015-04-01'
'Coral', 0, '2015-04-02'
'Coral', 0, '2015-04-03'
'Coral', 0, '2015-04-04'
'Coral', 0, '2015-04-05'
'Coral', 0, '2015-04-06'
'Coral', 0, '2015-04-07'
'Coral', 0, '2015-04-08'
'Coral', 0, '2015-04-09'
'Coral travel', 0, '2015-04-01'
'Coral travel', 0, '2015-04-02'
'Coral travel', 0, '2015-04-03'
'Coral travel', 0, '2015-04-04'
'Coral travel', 0, '2015-04-05'
'Coral travel', 0, '2015-04-06'
'Coral travel', 0, '2015-04-07'
'Coral travel', 0, '2015-04-08'
'Coral travel', 0, '2015-04-09'
'Egypt last minute', 1, '2015-04-01'
'Egypt last minute', 1, '2015-04-02'
'Egypt last minute', 0, '2015-04-03'
'Egypt last minute', 0, '2015-04-04'
'Egypt last minute', 0, '2015-04-05'
'Egypt last minute', 0, '2015-04-06'
'Egypt last minute', 0, '2015-04-07'
'Egypt last minute', 0, '2015-04-08'
'Egypt last minute', 0, '2015-04-09'
'Egypt holidays', 0, '2015-04-01'
'Egypt holidays', 0, '2015-04-02'
'Egypt holidays', 0, '2015-04-03'
'Egypt holidays', 0, '2015-04-04'
'Egypt holidays', 0, '2015-04-05'
'Egypt holidays', 0, '2015-04-06'
'Egypt holidays', 0, '2015-04-07'
'Egypt holidays', 0, '2015-04-08'
'Egypt holidays', 0, '2015-04-09'
'Exim', 0, '2015-04-01'
'Exim', 0, '2015-04-02'
'Exim', 0, '2015-04-03'
'Exim', 0, '2015-04-04'
'Exim', 0, '2015-04-05'
'Exim', 0, '2015-04-06'
'Exim', 0, '2015-04-07'
'Exim', 0, '2015-04-08'
'Exim', 0, '2015-04-09'
'Greece last minute', 0, '2015-04-03'
'Greece last minute', 0, '2015-04-04'
'Greece last minute', 0, '2015-04-05'
'Greece last minute', 0, '2015-04-06'
'Greece last minute', 0, '2015-04-07'
'Greece last minute', 0, '2015-04-08'
'Greece last minute', 0, '2015-04-09'
'Greece holidays', 0, '2015-04-01'
'Greece holidays', 0, '2015-04-02'
'Greece holidays', 0, '2015-04-03'
'Greece holidays', 0, '2015-04-04'
'Greece holidays', 0, '2015-04-05'
'Greece holidays', 0, '2015-04-06'
'Greece holidays', 0, '2015-04-07'
'Greece holidays', 0, '2015-04-08'
'Greece holidays', 0, '2015-04-09'
'Last-minute', 0, '2015-04-01'
'Last-minute', 0, '2015-04-02'
'Last-minute', 0, '2015-04-03'
'Last-minute', 0, '2015-04-04'
'Last-minute', 0, '2015-04-05'
'Last-minute', 0, '2015-04-06'
'Last-minute', 0, '2015-04-07'
'Last-minute', 0, '2015-04-08'
'Last-minute', 0, '2015-04-09'
'Last Minute', 1, '2015-04-01'
'Last Minute', 1, '2015-04-02'
'Last Minute', 0, '2015-04-03'
'Last Minute', 0, '2015-04-04'
'Last Minute', 0, '2015-04-05'
'Last Minute', 0, '2015-04-06'
'Last Minute', 0, '2015-04-07'
'Last Minute', 0, '2015-04-08'
'Last Minute', 0, '2015-04-09'
'Last Minute Egypt', 0, '2015-04-01'
'Last Minute Egypt', 0, '2015-04-02'
'Last Minute Egypt', 0, '2015-04-03'
'Last Minute Egypt', 0, '2015-04-04'
'Last Minute Egypt', 0, '2015-04-05'
'Last Minute Egypt', 0, '2015-04-06'
'Last Minute Egypt', 0, '2015-04-07'
'Last Minute Egypt', 0, '2015-04-08'
'Last Minute Egypt', 0, '2015-04-09'
'Last minute holidays', 0, '2015-04-01'
'Last minute holidays', 0, '2015-04-02'
'Last minute holidays', 0, '2015-04-03'
'Last minute holidays', 0, '2015-04-04'
'Last minute holidays', 0, '2015-04-05'
'Last minute holidays', 0, '2015-04-06'
'Last minute holidays', 0, '2015-04-07'
'Last minute holidays', 0, '2015-04-08'
'Last minute holidays', 0, '2015-04-09'
'Offer holiday', 0, '2015-04-01'
'Offer holiday', 0, '2015-04-02'
'Offer holiday', 0, '2015-04-03'
'Offer holiday', 0, '2015-04-04'
'Offer holiday', 0, '2015-04-05'
'Offer holiday', 0, '2015-04-06'
'Offer holiday', 0, '2015-04-07'
'Offer holiday', 0, '2015-04-08'
'Offer holiday', 0, '2015-04-09'
'Sun & fun', 0, '2015-04-01'
'Sun & fun', 0, '2015-04-02'
'Sun & fun', 0, '2015-04-03'
'Sun & fun', 0, '2015-04-04'
'Sun & fun', 0, '2015-04-05'
'Sun & fun', 0, '2015-04-06'
'Sun & fun', 0, '2015-04-07'
'Sun & fun', 0, '2015-04-08'
'Sun & fun', 2, '2015-04-09'
'Sun and fun', 0, '2015-04-01'
'Sun and fun', 0, '2015-04-02'
'Sun and fun', 0, '2015-04-03'
'Sun and fun', 0, '2015-04-04'
'Sun and fun', 0, '2015-04-05'
'Sun and fun', 0, '2015-04-06'
'Sun and fun', 0, '2015-04-07'
'Sun and fun', 0, '2015-04-08'
'Sun and fun', 1, '2015-04-09'
'Lodge', 0, '2015-04-01'
'Lodge', 0, '2015-04-02'
'Lodge', 0, '2015-04-03'
'Lodge', 0, '2015-04-04'
'Lodge', 0, '2015-04-05'
'Lodge', 1, '2015-04-06'
'Lodge', 1, '2015-04-07'
'Lodge', 1, '2015-04-08'
'Lodge', 0, '2015-04-09'
'Holiday greece', 0, '2015-04-01'
'Holiday greece', 0, '2015-04-02'
'Holiday greece', 0, '2015-04-03'
'Holiday greece', 0, '2015-04-04'
'Holiday greece', 0, '2015-04-05'
'Holiday greece', 0, '2015-04-06'
'Holiday greece', 0, '2015-04-07'
'Holiday greece', 0, '2015-04-08'
'Holiday greece', 0, '2015-04-09'
'Holiday', 4, '2015-04-01'
'Holiday', 4, '2015-04-02'
'Holiday', 1, '2015-04-03'
'Holiday', 1, '2015-04-04'
'Holiday', 1, '2015-04-05'
'Holiday', 3, '2015-04-06'
'Holiday', 3, '2015-04-07'
'Holiday', 3, '2015-04-08'
'Holiday', 3, '2015-04-09'
'Wakacje.pl', 13, '2015-04-01'
'Wakacje.pl', 13, '2015-04-02'
'Wakacje.pl', 1, '2015-04-03'
'Wakacje.pl', 1, '2015-04-04'
'Wakacje.pl', 1, '2015-04-05'
'Wakacje.pl', 6, '2015-04-06'
'Wakacje.pl', 6, '2015-04-07'
'Wakacje.pl', 6, '2015-04-08'
'Wakacje.pl', 4, '2015-04-09'
'Holiday 2014', 0, '2015-04-01'
'Holiday 2014', 0, '2015-04-02'
'Holiday greece', 1, '2015-04-01'
'Holiday greece', 1, '2015-04-02'
'Holiday greece', 0, '2015-04-03'
'Holiday greece', 0, '2015-04-04'
'Holiday greece', 0, '2015-04-05'
'Holiday greece', 0, '2015-04-06'
'Holiday greece', 0, '2015-04-07'
'Holiday greece', 0, '2015-04-08'
'Holiday greece', 0, '2015-04-09'
'Last-minute holiday', 0, '2015-04-01'
'Last-minute holiday', 0, '2015-04-02'
'Last-minute holiday', 0, '2015-04-03'
'Last-minute holiday', 0, '2015-04-04'
'Last-minute holiday', 0, '2015-04-05'
'Last-minute holiday', 0, '2015-04-06'
'Last-minute holiday', 0, '2015-04-07'
'Last-minute holiday', 0, '2015-04-08'
'Last-minute holiday', 1, '2015-04-09'
'Holiday last minute', 0, '2015-04-01'
'Holiday last minute', 0, '2015-04-02'
'Holiday last minute', 0, '2015-04-03'
'Holiday last minute', 0, '2015-04-04'
'Holiday last minute', 0, '2015-04-05'
'Holiday last minute', 0, '2015-04-06'
'Holiday last minute', 0, '2015-04-07'
'Holiday last minute', 0, '2015-04-08'
'Holiday last minute', 0, '2015-04-09'
'Vacation en', 0, '2015-04-01'
'Vacation en', 0, '2015-04-02'
'Vacation en', 0, '2015-04-03'
'Vacation en', 0, '2015-04-04'
'Vacation en', 0, '2015-04-05'
'Vacation en', 0, '2015-04-06'
'Vacation en', 0, '2015-04-07'
'Vacation en', 0, '2015-04-08'
'Vacation en', 0, '2015-04-09'
'Holiday in Greece', 0, '2015-04-01'
'Holiday in Greece', 0, '2015-04-02'
'Holiday in Greece', 0, '2015-04-03'
'Holiday in Greece', 0, '2015-04-04'
'Holiday in Greece', 0, '2015-04-05'
'Holiday in Greece', 0, '2015-04-06'
'Holiday in Greece', 0, '2015-04-07'
'Holiday in Greece', 0, '2015-04-08'
'Holiday in Greece', 1, '2015-04-09'
'Holidays abroad', 0, '2015-04-01'
'Holidays abroad', 0, '2015-04-02'
'Holidays abroad', 0, '2015-04-03'
'Holidays abroad', 0, '2015-04-04'
'Holidays abroad', 0, '2015-04-05'
'Holidays abroad', 0, '2015-04-06'
'Holidays abroad', 0, '2015-04-07'
'Holidays abroad', 0, '2015-04-08'
'Holidays abroad', 0, '2015-04-09'
'Holiday', 0, '2015-04-01'
'Holiday', 0, '2015-04-02'
'Holiday', 1, '2015-04-03'
'Holiday', 1, '2015-04-04'
'Holiday', 1, '2015-04-05'
'Holiday', 0, '2015-04-06'
'Holiday', 0, '2015-04-07'
'Holiday', 0, '2015-04-08'
'Holiday', 0, '2015-04-09'
'Egypt holiday last minute', 0, '2015-04-01'
'Egypt holiday last minute', 0, '2015-04-02'
'Egypt holiday last minute', 0, '2015-04-03'
'Egypt holiday last minute', 0, '2015-04-04'
'Egypt holiday last minute', 0, '2015-04-05'
'Egypt holiday last minute', 0, '2015-04-06'
'Egypt holiday last minute', 0, '2015-04-07'
'Egypt holiday last minute', 0, '2015-04-08'
'Egypt holiday last minute', 0, '2015-04-09'
'Holiday greece', 0, '2015-04-01'
'Holiday greece', 0, '2015-04-02'
'Holiday greece', 0, '2015-04-03'
'Holiday greece', 0, '2015-04-04'
'Holiday greece', 0, '2015-04-05'
'Holiday greece', 0, '2015-04-06'
'Holiday greece', 0, '2015-04-07'
'Holiday greece', 0, '2015-04-08'
'Holiday greece', 0, '2015-04-09'
'Travel abroad', 0, '2015-04-01'
'Travel abroad', 0, '2015-04-02'
'Travel abroad', 0, '2015-04-03'
'Travel abroad', 0, '2015-04-04'
'Travel abroad', 0, '2015-04-05'
'Travel abroad', 1, '2015-04-06'
'Travel abroad', 1, '2015-04-07'
'Travel abroad', 1, '2015-04-08'
'Travel abroad', 0, '2015-04-09'
'Vizier holidays', 1, '2015-04-01'
'Vizier holidays', 1, '2015-04-02'
'Vizier holidays', 0, '2015-04-03'
'Vizier holidays', 0, '2015-04-04'
'Vizier holidays', 0, '2015-04-05'
'Vizier holidays', 0, '2015-04-06'
'Vizier holidays', 0, '2015-04-07'
'Vizier holidays', 0, '2015-04-08'
'Vizier holidays', 0, '2015-04-09'
'Www vacation en ', 0,' 2015-04-01 '
'Www vacation en ', 0,' 2015-04-02 '
'Www vacation en ', 0,' 2015-04-03 '
'Www vacation en ', 0,' 2015-04-04 '
'Www vacation en ', 0,' 2015-04-05 '
'Www vacation en ', 0,' 2015-04-06 '
'Www vacation en ', 0,' 2015-04-07 '
'Www vacation en ', 0,' 2015-04-08 '
'Www vacation en ', 1,' 2015-04-09 '
'Trips', 0, '2015-04-01'
'Trips', 0, '2015-04-02'
'Trips', 0, '2015-04-03'
'Trips', 0, '2015-04-04'
'Trips', 0, '2015-04-05'
'Trips', 0, '2015-04-06'
'Trips', 0, '2015-04-07'
'Trips', 0, '2015-04-08'
'Trips', 0, '2015-04-09'
'Tours', 0, '2015-04-01'
'Tours', 0, '2015-04-02'
'Tours', 0, '2015-04-03'
'Tours', 0, '2015-04-04'
'Tours', 0, '2015-04-05'
'Tours', 0, '2015-04-06'
'Tours', 0, '2015-04-07'
'Tours', 0, '2015-04-08'
'Tours', 0, '2015-04-09'
'Trips abroad', 0, '2015-04-01'
'Trips abroad', 0, '2015-04-02'
'Trips abroad', 0, '2015-04-03'
'Trips abroad', 0, '2015-04-04'
'Trips abroad', 0, '2015-04-05'
'Trips abroad', 0, '2015-04-06'
'Trips abroad', 0, '2015-04-07'
'Trips abroad', 0, '2015-04-08'
'Trips abroad', 0, '2015-04-09'
It is a part of the Google Analytics API report.
KEYWORD column contains keywords used to advertise site,
CONVERSIONS column has number of conversions at day named in CREATED_AT.
The specific predicate is date range:
WHERE '2015-04-01' <= created_at AND created_at <= '2015-04-09'
The query I try to write should print the amount of days since last conversion and date of last conversion (or null/'never' if there was no conversion in date range). So results I'm looking for look like this for today (CURRENT_DATE = '2015-04-10'):
KEYWORD, DAYS_SINCE_CONVERSION, LAST_CONVERSION_DATE
'Travel agent', null, null
'Travel agency vizier', 1, '2015-04-09'
'Egypt last minute', 8, '2015-04-02'
'Exim', null, null
'Last Minute', 8, '2015-04-02'
'Sun & fun', 1, '2015-04-09'
... (and so on - I gave here few keywords just for example)
I tried some SQLs but without any luck, I don't have anything to share here because I wasn't even close to desired result. :-(
Upvotes: 1
Views: 78
Answers (2)
mikewolf78
Reputation: 86
Here's my approach to this (probably database-independent, but don't take my word on it):
select c.keyword, t.days_since, t.last_date from conversation c
left join (
select keyword, current_date - max(created_at) as days_since, max(created_at) as last_date
from conversation
where created_at between '2015-04-01' and '2015-04-09'
and conversations > 0
group by keyword
) t on t.keyword = c.keyword
group c.keyword, t.days_since, t.last_date
Upvotes: 1
user330315
Reputation:
You tagged your question with postgresql-9.4 so you can use a filtered aggregate for this:
select keyword,
max(created_at) filter (WHERE conversions > 0 AND created_at between '2015-04-01' AND '2015-04-09') as last_conversion_date
from conversion
group by keyword;
This will return one row for each keyword and the latest created_at date in the given time range but only if there was a conversion (that is necessary to filter out, e.g. 'Travel agent', 0, '2015-04-09'
To calculate the number of days since then, simply subtract that latest date from current_date. In order to not repeat the expression it's easier to put the query into a derived table for this:
select keyword,
current_date - last_conversion_date as days_since_conversion,
last_conversion_date
from (
select keyword,
max(created_at) filter (WHERE conversions > 0 AND created_at between '2015-04-01' AND '2015-04-09') as last_conversion_date
from conversion
group by keyword
) t
order by keyword;
date '2015-04-01' is the SQL standard's syntax for writing a date literal I prefer it over the implicit date style where only a string is used.
SQLFiddle here: http://sqlfiddle.com/#!15/d9aff/1
The SQLFiddle uses a case when expression instead of the filter expression because filter isn't available in earlier Postgres versions.
Upvotes: 2
Related Questions
- How to get only last row if condition is met within 30 days span
- How to get the last occurrence of a date?
- count number of occurences before a certain date
- Query based on number of records on distinct days between two dates
- how to retrieve results for past n days using postgresql
- Query to find days since last value of a column is continuously observed - PostgreSQL
- Select last N days till max(date) in Postrges?
- SQL find consecutive days of specific threshold reached
- SQL query, return rows based number of date entries
- PostgreSQL: find number of consecutive days up until now