Filtering to only show the most recent event dependent on another value in SQL

user_14240319 0
Viewed 29

I've been struggling to get my hand around the following scenario where I can't find a similar question with a similar scenario asked, so here we go:

Let's say I have two tables:

Messages:

P_ID Message_ID Message_sent_date
123 ABCD 2020/03/01
123 BCDE 2020/07/01
234 CDEF 2020/01/01
234 DEFG 2020/05/01

People:

P_ID P_Achievement Achievement_date
123 Level 1 2019/09/01
123 Level 4 2020/06/01
234 Level 2 2019/12/01
234 Level 3 2020/04/01

I want to join people on messages with P_ID, BUT with the condition that only the most recent achievement relevant to the message_sent_date is displayed from P_ID. So it should look like this:

P_ID Message_ID Message_sent_date P_achievement Achievment_date
123 ABCD 2020/03/01 Level 1 2019/09/01
123 BCDE 2020/07/01 Level 4 2020/06/01
234 CDEF 2020/01/01 Level 2 2019/12/01
234 DEFG 2020/05/01 Level 3 2020/04/01

My current code actually works, however, the problem is that the messages and people data tables in my real-life problem are incredibly large so the query takes more than an hour to run (I actually never fully finished running it because it takes too long, but tried with a specific P_ID example where it works). Filtering the two original tables doesn't help much.

I know that the subquery in the where is causing the long run time so I was wondering if anyone knows how to solve this type of problem in a more efficient way?

Thanks already in advance!

SELECT *
FROM messages m
LEFT JOIN people p
    ON m.P_ID = p.P_ID
WHERE Achievment_date = (SELECT MAX(Achievement_date)
                    FROM people
                    WHERE Message_sent_date >= Achievement_date
                    )
presto subquery sql
1 Answers

Tested in PostgreSQL v14, should be the same for Presto.

CREATE TABLE messages (
  "p_id" INTEGER,
  "message_id" VARCHAR(4),
  "message_sent_date" TIMESTAMP
);

INSERT INTO messages
  ("p_id", "message_id", "message_sent_date")
VALUES
  ('123', 'ABCD', '2020/03/01'),
  ('123', 'BCDE', '2020/07/01'),
  ('234', 'CDEF', '2020/01/01'),
  ('234', 'DEFG', '2020/05/01');

CREATE TABLE people (
  "p_id" INTEGER,
  "p_achievement" VARCHAR(7),
  "achievement_date" TIMESTAMP
);

INSERT INTO people
  ("p_id", "p_achievement", "achievement_date")
VALUES
  ('123', 'Level 1', '2019/09/01'),
  ('123', 'Level 4', '2020/06/01'),
  ('234', 'Level 2', '2019/12/01'),
  ('234', 'Level 3', '2020/04/01');

Query: Don't do the ORDER BY if you're looking for speed, that was just to get it to match your result.

SELECT p_id, message_id, Message_sent_date, p_achievement, achievement_date
FROM (
  SELECT p_id, message_id, MIN(Message_sent_date) Message_sent_date, MAX(achievement_date) achievement_date
  FROM messages m
  LEFT JOIN people p
  USING(p_id)
  WHERE message_sent_date >= achievement_date
  GROUP BY p_id, message_id
  ) t
JOIN people
USING(p_id, achievement_date)
ORDER BY p_id, message_id
p_id message_id message_sent_date p_achievement achievement_date
123 ABCD 2020-03-01 Level 1 2019-09-01
123 BCDE 2020-07-01 Level 4 2020-06-01
234 CDEF 2020-01-01 Level 2 2019-12-01
234 DEFG 2020-05-01 Level 3 2020-04-01

View on DB Fiddle

user_118659560
Related