'Redshift SQL Query Between Current Date and 7 days ago
I've been trying to filter the data for the last X number of days. All these columns work as standalone results when I remove the time filter from the where clause.
I keep on getting the error of no results when I join and add a time filter in where clause.
SELECT x.datex, Signups, Page_load FROM (SELECT CAST (mp_date AS DATE) AS datex, mp_event_name, COUNT(DISTINCT mp_device_id) AS Signups
FROM mp_master_event
WHERE mp_event_name = 'email_page_submit' AND datex between CURRENT_DATE AND CURRENT_DATE - INTERVAL '7 DAY'
GROUP BY mp_event_name, datex
ORDER BY datex DESC) x JOIN (SELECT CAST(mp_date AS DATE) AS datex, mp_event_name, COUNT(DISTINCT mp_device_id) AS Page_load
FROM mp_master_event
WHERE mp_event_name = 'home_page_load_confirm' AND datex between CURRENT_DATE AND CURRENT_DATE - INTERVAL '7 DAY'
GROUP BY datex, mp_event_name
ORDER BY datex DESC) y ON x.datex = y.datex
What should I do? Also since the table is huge, it's taking a lot of time to query and this is just 10% of what I want to achieve with the whole query. Any suggestions are appreciated.
Solution 1:[1]
Got it :)
datex >= DATE(dateadd(DAY,-7, current_date))
SELECT x.datex, Signups, Page_load FROM (SELECT CAST (mp_date AS DATE) AS datex, mp_event_name, COUNT(DISTINCT mp_device_id) AS Signups
FROM mp_master_event
WHERE mp_event_name = 'email_page_submit' AND datex >= DATE(dateadd(DAY,-7, current_date))
GROUP BY mp_event_name, datex
ORDER BY datex DESC) x JOIN (SELECT CAST(mp_date AS DATE) AS datex, mp_event_name, COUNT(DISTINCT mp_device_id) AS Page_load
FROM mp_master_event
WHERE mp_event_name = 'home_page_load_confirm' AND datex >= DATE(dateadd(DAY,-7, current_date))
GROUP BY datex, mp_event_name
ORDER BY datex DESC) y ON x.datex = y.datex
Solution 2:[2]
datex between CURRENT_DATE AND CURRENT_DATE - INTERVAL '7 DAY'
should be
datex between CURRENT_DATE - INTERVAL '7 DAY' AND CURRENT_DATE
earlier date should be first
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | Mohit Jain |
Solution 2 | Codemaker |