Market Basket One Gigantic Query for ServicePad

marketbasket_all_in_one.sql

 SELECT event_id as recommended_event_id FROM (
	(SELECT event1 AS event_id FROM (SELECT tempP.event1 FROM (
SELECT r1.event AS event1, r2.event AS event2
FROM (SELECT user_id AS user, event_id AS event
FROM service_serviceenrollment WHERE event_id IN (
	SELECT event_id FROM service_serviceenrollment GROUP BY event_id HAVING COUNT(*) >= threshold
)) AS r1, (SELECT user_id AS user, event_id AS event
FROM service_serviceenrollment WHERE event_id IN (
	SELECT event_id FROM service_serviceenrollment GROUP BY event_id HAVING COUNT(*) >= threshold
)) AS r2
WHERE r1.user = r2.user
AND r1.event < r2.event
GROUP BY r1.event, r2.event
HAVING COUNT(*) >= threshold) AS tempP
JOIN service_serviceenrollment ON (service_serviceenrollment.event_id = tempP.event1 OR service_serviceenrollment.event_id = tempP.event2 )
WHERE service_serviceenrollment.user_id = a_user_id) AS list1 WHERE
	event1 NOT IN (
		SELECT event_id FROM service_serviceenrollment WHERE user_id = a_user_id
	))
	union
	(SELECT event2 AS event_id FROM (SELECT tempP.event2 FROM (
SELECT r1.event AS event1, r2.event AS event2
FROM (SELECT user_id AS user, event_id AS event
FROM service_serviceenrollment WHERE event_id IN (
	SELECT event_id FROM service_serviceenrollment GROUP BY event_id HAVING COUNT(*) >= threshold
)) AS r1, (SELECT user_id AS user, event_id AS event
FROM service_serviceenrollment WHERE event_id IN (
	SELECT event_id FROM service_serviceenrollment GROUP BY event_id HAVING COUNT(*) >= threshold
)) AS r2
WHERE r1.user = r2.user
AND r1.event < r2.event
GROUP BY r1.event, r2.event
HAVING COUNT(*) >= threshold) AS tempP
JOIN service_serviceenrollment ON (service_serviceenrollment.event_id = tempP.event1 OR service_serviceenrollment.event_id = tempP.event2 )
WHERE service_serviceenrollment.user_id = a_user_id) AS list2 WHERE
	event2 NOT IN (
		SELECT event_id FROM service_serviceenrollment WHERE user_id = a_user_id
	))
) AS final;