SQL query to get prior month in Athena

Question

I want to get last month data (fist day of month through last day of month). My data is in UT (universal time) and need to pull in ET (July). I need to count the number or purchases (by counting the number of rows) and returns, grouped by ID and Date/month I'm using the below query and getting an error on the count*..line (the field is Boolean)

select 
date_format(date_trunc('day',at_timezone(DATE, 'America/New_York')),'%Y-%m') as DATE_NORM
, ID
, count (purchases)
, count* (case when Returns = 1 then 1 else O End)
from "ABC"."DFG"
where date_format(date_trunc('day',at_timezone(DATE, 'America/New_York')),'%Y-%m') >= date_format(date_add('day',-7, current_date) 
and ID IN ('011','012')
group by 1,2;

How can I fix the query so every month I run it, it will provide the latest month data? (In this case July), and next month will provide Jul-Aug?

I'm expecting to count the numbers or purchases rows and returns

Date	ID	Purchases	Returns
2024-07	011	33	0
2024-07	012	57	33

Prabhat · Accepted Answer · 2024-08-27 14:33:59Z

First of all your SQL query has multiple errors and mainly in the syntax and logic used to count the purchases and returns.

The count syntax used is incorrect because you should have used SUM for the conditional count of returns and then in order to filter the last months data you should have used data_trunc and at_timezone to ensure the data is correctly pulled based on Eastern Time (ET). You can also used last months data so that it should always fetch the latest completed months data when it runs.

Check out the one which I am listing below for your reference:

SELECT 
    date_format(date_trunc('month', at_timezone(DATE, 'America/New_York')), '%Y-%m') AS DATE,
    ID,
    COUNT(*) AS Purchases,
    SUM(CASE WHEN Returns = 1 THEN 1 ELSE 0 END) AS Returns
FROM 
    "ABC"."DFG"
WHERE 
    date_format(date_trunc('month', at_timezone(DATE, 'America/New_York')), '%Y-%m') = date_format(date_trunc('month', current_date AT TIME ZONE 'America/New_York' - INTERVAL '1' MONTH), '%Y-%m')
    AND ID IN ('011', '012')
GROUP BY 
    DATE, ID;

Check this one, I hope this will resolve your query.

Collectives™ on Stack Overflow

SQL query to get prior month in Athena

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related