1

Consider a PostgreSQL table (pseudo-sql):

CREATE TABLE events (
  time TIMESTAMPTZ,
  success BOOLEAN,
  foreign_id FOREIGN KEY,
  …
);

Sample prepared dbfiddle

I can run a query to get the last event for each foreign_id to essentially get its current status. I can therefore see that at NOW() there are N foreign_ids in the success state. AFAIK this involves applying a ROW_NUMBER() over a window grouped by foreign_id then filtering for row_number = 1, or SELECT DISTINCT ON (foreign_id) … ORDER BY time DESC.

What I would like is the total count of foreign_ids in "success" state for each day in the past x days.

So for the count for 3 days ago, I would only consider events rows with a time up until 3 days ago, then take the latest/final row for each foreign_id to get its current status 3 days ago, then of all of those rows, count how many were in success state.

The key thing is that this count would go up and down over time, for example maybe 3 days ago there were 10 foreign_ids in success state but right now there are only 4. I would like to capture this.

So ideally I get back something that looks like:

SELECT "day", foreign_ids_in_success_state …

With data that looks something like this:

+-----+------------------------------------------------------------+
| Day | Count of Events with 'successful' status up until that day |
+-----+------------------------------------------------------------+
| 1   |                             10                             |
+-----+------------------------------------------------------------+
| 2   |                              5                             |
+-----+------------------------------------------------------------+
| 3   |                              7                             |
+-----+------------------------------------------------------------+
| 4   |                              2                             |
+-----+------------------------------------------------------------+

So what I want is:

  1. for each day irrespective of whether there are events on that day or not
  2. get the latest event for each foreign_id up until that day, e.g. for the day 3 days ago, pretend NOW() is 3 days ago, then get what would be the latest event for each foreign_id 3 days ago, i.e. disregard any rows newer than 3 days ago
  3. of all of those events, count how many were success IS TRUE

I believe some CROSS JOINs with generate_series() may be required for this kind of thing but I'm having trouble reasoning about it.

If this kind of thing has a name or relevant terms, I would appreciate learning it/them since I feel like it might not be a unique/rare situation.

6
  • @BrendanMcCaffrey Hello! I have updated the fiddle. Please let me know if that helps and if you would like any further changes or have any other questions/comments! Mar 4, 2022 at 22:25
  • I see what you mean. I haven't gotten it to work in fiddle. The fiddle demonstrates the schema, the kind of data, and how I can obtain the results for the current point in time i.e. NOW(), but I would like to compute this for each day going back say e.g. 30 days. I left a comment referring to this in the final block. I will edit my post with a mockup of the expected output but I'm not sure how helpful it'd be. Please let me know what you think though and I will do my best. Mar 4, 2022 at 22:31
  • Also, apologies but I have made a final tweak to the final panel in the fiddle. I've updated the links in the comment and the post, but also here. I basically moved the WHERE success IS TRUE out of the CTE because I think that may have been incorrect. Mar 4, 2022 at 22:32
  • I have added a sample table output. Mar 4, 2022 at 22:37
  • I agree. I was trying to find a way to fix that data in place. I'll just copy paste values for now. May take me a few minutes. Mar 4, 2022 at 22:52

1 Answer 1

1

Would this be what you're looking for?

SELECT date_trunc('day', ts) AS "day", COUNT(*)
FROM events
WHERE success IS TRUE
GROUP BY date_trunc('day', ts)
ORDER BY Day DESC

Fiddle

5
  • Thank you, upvoted, but I see now where the confusion lies. I believe this will give me the count of "successful" events on each day. What I want is: 1) for each day irrespective of whether there are events on that day or not, 2) get the latest event for each foreign_id 3) of all of those events, count how many were success IS TRUE. I will add this to my post. Please tell me if that remains unclear. Mar 4, 2022 at 22:36
  • Yeah, I was afraid this wasn't exactly what you were looking for. Mar 4, 2022 at 22:38
  • I should clarify number 2) "get the latest event for each foreign_id up until that day". i.e., if it were that day right now, get the latest state for each foreign_id as if we didn't see any events after that day Mar 4, 2022 at 22:39
  • I think the first sentence is correct. So this is a table of point-in-time statuses for each foreign key right. So maybe yesterday foreign_key 2 has a row for success = false because it failed, but then today it ran again and it succeeded so there's a row today that says success = true. Knowing this, for each foreng_key we can obtain what its "last known" status was. Then we can count how many are last-known to be successful. The trick is that I want to know this "last known" status for each day going back 30 days. Mar 4, 2022 at 22:48
  • For example, if we went back in time to 3 days ago (i.e. disregard rows newer than 3 days ago), what would the "last known" successful count have been. I want this count for each day going back 30 days ago. Mar 4, 2022 at 22:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.