Optimize your use of interval functions

Question

I have to do the following:

Rank users with persona = 'z' in order from highest to lowest last week.

I wrote the following code:

SELECT U.*, SUM(T.amount) AS total_spends
FROM User U
JOIN Transact T ON U.id = T.created_by
WHERE U.persona = 'Z' AND T.date_created >= CURRENT_DATE - INTERVAL '1 week'
GROUP BY U.id
ORDER BY total_spends DESC;

However, I get the following error: Error: near "'1 week'": syntax error

Any help would be greatly appreciated.

Answer 1

SELECT U.*, SUM(T.amount) AS total_spends FROM User U JOIN Transact T ON U.id = T.created_by WHERE U.persona = 'Z' AND T.date_created >= DATE_SUB(CURRENT_DATE, INTERVAL 1 WEEK) GROUP BY U.id ORDER BY total_spends DESC;