CODEWITHSUNDEEP
sqlamazon-redshift
Chip
Chip

Reputation: 1

How do I get Trailing 12 Months for a specific month but dynamic year?

I'm using January as an example to try and simplify this.

Here is the original query:

SELECT 
  ASIN
, SUM(CASE WHEN search_date BETWEEN DATEADD(month, -12, '2022-01-31')  AND '2022-01-31' THEN all_searches ELSE 0 END) jan_ttm_searches
FROM search_table 
WHERE search_date BETWEEN DATEADD(month, -12, '2022-01-31')  AND '2022-01-31'
GROUP BY 1;

What I'm trying to do is take this query and modify it to run based on the current year instead of a static date. So, for example:

I need '2022-01-31' to adapt to the current date instead of being static.

ASIN is a number/character identifier. Search Date example is 2022-11-24 00:00:00. All_searches is the number of searches for that asin on that date.

I've tried to put an EXTRACT in the CASE WHEN, but that errored for nested aggregates.

CASE WHEN search_date between dateadd(month, -12, TRUNC(MAX(search_date)))) AND TRUNC(MAX(search_date)) 
            AND EXTRACT(MONTH FROM search_date) = 1 AND EXTRACT(YEAR FROM search_date) = EXTRACT(YEAR FROM CURRENT_DATE)

I tried using RUN_DATE, which gets me TTM from the current date but not from January.

case when search_date BETWEEN ADD_MONTHS(TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD'), -12) AND TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD') then all_searches else 0 end

I tried googling similar issues but couldn't find a solution that worked for what I'm trying to do.

The query is going to Redshift. Any help is appreciated. Thank you.

Edited to add: Desired output is one column with ASIN and one column with the total number of searches for that month. The original query works except for the static date. This is intended to run on a schedule every month and needs to be reactive to the current year/month.

Upvotes: 0

Views: 863

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11102

I think you have answered your own question. You cannot nest aggregates but you can compute the MAX() in a subquery. You can do this a couple of ways:

  1. Compute the max in a CTE or subquery and use this result for the CASE statement. You can do this with the MAX() window function so that all the originals rows will be present for the CASE statement.
  2. Compute the MAX() and CROSS JOIN this single value to original table.

#1 is likely better for large tables as it only needs to scan the table once while #2 scans the table twice.

==========================================================

Since you haven't provided any data or expected results I've made up some data and a process to achieve something like what you are looking for. This is likely not exact but will show how to generate the date limit you are looking for from the source table.

I also changed things so that the SQL will run in Postgres as well as Redshift so you use a sql fiddle to play with the code if you like.

Set up data:

create table test (
  ASIN varchar(16),
  search_date date,
  all_searches int
);

insert into test values
  ('abc', '2021-01-15', 5),
  ('def', '2021-02-15', 4),
  ('ghi', '2021-03-15', 3),
  ('jkl', '2021-04-15', 2),
  ('mno', '2021-05-15', 1),
  ('abc', '2021-06-15', 10),
  ('def', '2021-07-15', 11),
  ('ghi', '2021-08-15', 12),
  ('jkl', '2021-09-15', 13),
  ('mno', '2021-10-15', 14),
  ('abc', '2022-01-15', 9),
  ('def', '2022-01-15', 8),
  ('ghi', '2022-01-15', 7),
  ('jkl', '2022-01-15', 6),
  ('mno', '2022-01-15', 5)
  ;

Query:

WITH max_date AS (
  SELECT *, 
      DATE_TRUNC('month', MAX(search_date) OVER ()) 
          + interval '1 month' - interval '1 day' as max_date
  FROM test
)
SELECT 
  ASIN
, SUM(
      CASE WHEN search_date BETWEEN max_date - interval '12 months' 
          AND max_date
      THEN all_searches 
      ELSE 0 END) jan_ttm_searches
FROM max_date
WHERE search_date BETWEEN '2022-01-31'::date - interval '12 months' 
    AND '2022-01-31'::date
GROUP BY 1
ORDER by 1;

SQL Fiddle for play with if you like:

http://sqlfiddle.com/#!15/3e0e2/13

Like I said this isn't likely the exact result you are looking for but should get you started.

Upvotes: 0

Related Questions