Search code examples
sqlteradata

Finding the number of previous tickets in the last 30 days

I'm trying to add a column for "number of tickets in the last 30 days" to a query of all tickets in 2024. What I've got so far:

SELECT customer_id,
       ticket_number,
       ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY receive_dt DESC
       ) AS "Num Tickets Last 30 Days"
FROM tickets
WHERE EXTRACT(YEAR FROM receive_dt) = 2024

The above works great, except that it's giving me a count of all tickets for the year (b/c that's what my WHERE is looking at, of course).

Is there any way to restrict an Ordered Analytical Function by date instead of the number of rows? What should I be using instead?

Solution 
  • SELECT t0.customer_id,
   t0.ticket_number,
   (SELECT COUNT(1)
    FROM tickets t1
    WHERE t0.customer_id = t1.customer_id
    AND t1.receive_dt >= CURRENT_DATE - INTERVAL '30' DAY
   ) AS "Num Tickets Last 30 Days"
FROM tickets t0
WHERE EXTRACT(YEAR FROM t0.receive_dt) = 2024