How do I query the max count from each week in the past?

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?

Many thanks!

Not sure what your table looks like but I'll give you an example of how it could be done.

Assuming you have an ID for each registration and date of it you could do the following:

SELECT COUNT(ID) as registrations, DATE_TRUNC('week',create_date) as registration_week
FROM registrations
GROUP BY DATE_TRUNC('week',create_date)

Though the following code needs adjustment depending on which SQL database you are using.
image

@stefancvrkotic Thank you so much for looking at this! Here is what my table looks like:

The DATE_TRUNC function looks super promising, but I can't figure out how to use it to get what I need - I want to find the maximum value of "count" for each week. The resulting table would look something like:

where week_past 1 would cover the IDs with create_date between today and 7 days ago, week_past 2 would be between 8 and 14 days ago, week_past 3 would be between 15 and 21 days ago, etc.

Does that clarify it?

If I'm reading the above correctly, your results seem a bit different, and I haven't been able to transpose it to get what I need yet.

The help is so much appreciated!

Depending on which type of database you are using there are equivalents to DATE_TRUNC(), DATEPART(), CONVERT() etc.

In PostgreSQL your code should look something like

SELECT MAX(count) AS registrations, DATE_TRUNC('week',create_date) as registration_date
FROM registrations
WHERE server_id = 329
GROUP BY DATE_TRUNC('week',create_date)

If you are using Oracle you'd need to extract year/week and group by it.

Additionally worth noting it's not best practice to use intervals from current date as graphs will shift over time and those periods will change. In comparison "DATE_TRUNC function uses the 'week' date part to return the date for the Monday of each week" thus why retaining historical changes week over week.

Does that help?

@stefancvrkotic
Excellent. This was perfect - I am working with PostgreSQL. Thank you so much for the help!