I've written a query, and I'm kinda sure that it does what it want. I'm thinking, "huh, I wonder if I know how Postgres works?". I'm going to list a specific assumption about this query below, and if that is wrong, please correct my understanding.
OVER (PARTITION BY) will execute against the data after the final
GROUP BY and
ORDER BY are executed, thereby limiting the records that are summed to those that are indeed
DISTINCT ON (a.id, scl.new_status_id).
Note: This is a Python string that will be formatted into a proper SQL query using a Python library, hence the
%s format characters. If that causes confusion for you, I will update it. Please assume that I am passing in arguments that make a valid SQL query.
SELECT DISTINCT ON (a.id, scl.new_status_id) scl.new_status_id ,(scl.change_date AT TIME ZONE %s)::date as d ,SUM(COUNT(*)) OVER (PARTITION BY scl.new_status_id order by (scl.change_date AT TIME ZONE %s)::date) FROM automated_responses_statuschangelogs scl JOIN application_app a ON scl.applicant_id = a.id WHERE a.applied_class in %s AND a.applied_track in %s AND a.applied_date <= %s AND a.applied_date > %s AND scl.new_status_id IN %s AND scl.new_status_id NOT IN %s GROUP BY a.id, scl.new_status_id, d ORDER BY a.id, scl.new_status_id, d desc