Rewrite query without use of lateral joins

Question

Is there a simpler way to express the query below, without the use of lateral joins (not supported by my ORM) ?

select 
    v.id,
    v.name,
    v.avatar,
    sent_at,
    opened_at,
    replied_at
from visitors v
left join auto_message_events ame on ame.visitor_id = v.id
left join lateral (
    select inserted_at as sent_at from auto_message_events ame
    where ame.visitor_id = v.id and ame.event = 'sent'
) as sent on true
left join lateral (
    select inserted_at as opened_at from auto_message_events ame
    where ame.visitor_id = v.id and ame.event = 'opened'
) as opened on true
left join lateral (
    select inserted_at as replied_at from auto_message_events ame
    where ame.visitor_id = v.id and ame.event = 'replied'
) as replied on true
where ame.auto_message_id = 99
limit 10

As always, some context would be helpful. Table definitions, Postgres version, cardinalities. Most importantly: is the combination `(visitor_id, event)` *unique* in `auto_message_events`? — Erwin Brandstetter, Oct 01 '16 at 23:19

score 0 · Answer 1 · answered Oct 01 '16 at 20:36

0

One method is conditional aggregation:

select v.id, v.name, v.avatar,
       max(case when  ame.event = 'sent' then ame.inserted_at end) as sent_at,
       max(case when  ame.event = 'opened' then ame.inserted_at end) as opened_at,
       max(case when  ame.event = 'replied' then ame.inserted_at end) as replied_at
from visitors v join
     auto_message_events ame 
     on ame.visitor_id = v.id
where ame.auto_message_id = 99
group by v.id
limit 10;

I'm not sure if the where clause is actually needed, because its purpose may be to only return one row per visitor in the original table.

answered Oct 01 '16 at 20:36

Gordon Linoff

1,242,037
58
646
786

Hi Gordon - Your link doesnt work. blog.data-miners.com. Can you share me the working one... I like the answers you post on stackoverflow and would like to know if you have any good learning material for solving complex sql questions. thank you. – Teja Oct 01 '16 at 20:53
@SOaddict . . . I would point you to my book "Data Analysis Using SQL and Excel". – Gordon Linoff Oct 01 '16 at 20:57
Can u help me answering this ques... http://stackoverflow.com/questions/39801594/how-to-get-new-and-returned-ytd-mtd-wtd-users-in-a-user-traffic-table – Teja Oct 01 '16 at 21:20

Rewrite query without use of lateral joins

1 Answers1