Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Way to short circuit logic in SQL(ite)?
/*
* table plays lists all plays by all users across time
* table affils lists who (fan) follows whom (contact) as of a given point of time
* table reposts lists who reposted what song at what time
* I want to know for each row in plays if the user was following someone who reposted the track
* I also want to know for each row in plays if the user followed someone who was the author of the track
* the author is frequently NULL
I think the below query is correct, but it looks like it realizes the full join of everyone who
may have reposted the track that the listener follows. I am wondering if there is a way to write it
so that logic is short circuited and the scan stops for that row of plays once one person who reposts is found
*/
SELECT rpd.listener_id, rpd.created_at, rpd.track_id, rpd.was_reposted,
afau.followed_author
FROM
(SELECT p.listener_id, p.created_at, p.track_id,
(count(rp.track_id) > 0) AS was_reposted
FROM plays p
LEFT JOIN affils a ON a.fan_id = p.listener_id AND a.created_at < p.created_at
LEFT JOIN reposts rp ON a.contact_id = rp.user_id AND
p.track_id = rp.track_id AND rp.created_at > a.created_at AND
rp.created_at < p.created_at
GROUP BY p.listener_id, p.created_at, p.track_id) AS rpd
JOIN -- should be one-to-one
(SELECT p.listener_id, p.created_at, p.track_id,
(count(a.contact_id) > 0) AS followed_author
FROM plays p
LEFT JOIN affils a ON p.listener_id = a.fan_id AND
a.created_at < p.created_at AND a.contact_id = p.author_id
GROUP BY p.listener_id, p.created_at, p.track_id) AS afau
ON rpd.listener_id = afau.listener_id AND
rpd.created_at = afau.created_at AND
rpd.track_id = afau.track_id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment