Redshift SQL Query Between Current Date and 7 days ago

user_15574699 0
Viewed 312

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.

amazon-redshift mysql sql
2 Answers

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
user_155746990 
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

user_189928310
Related