I have a data set that I can get via RestAPI or direct SQL DB connection that looks like this:
My goal is to extract the maximum value of "count" from each week, so I can look at the week over week growth.
This SQL query gives me what I want for the most recent week:
SELECT server_id,MAX(count) as max_count FROM registrations WHERE server_id IN (329) AND create_date::TIMESTAMP::DATE <= CURRENT_DATE AND create_date::TIMESTAMP::DATE <= CURRENT_DATE - INTERVAL '7' DAY GROUP BY server_id
I could build one query for each week and then join them, but obviously that is terribly cumbersome and inefficient.
Is there a way to query the max of each week's "slice" all in one query?