Cross Join Query Optimization Part 4

Cross Join Query Optimization and Lookup function in Tableau
#tableau #queryoptimization #Lookupfunction #lookup #joins #postgres #postgresql

Data and Query Details:
CREATE TABLE dim.student (
student_id VARCHAR(50), -- Replaces 'id', represents the student ID
active BOOLEAN -- Whether the student is still active in the admission process
);

CREATE TABLE bi.admission_calls (
call_time TIMESTAMP, -- Replaces 'callstarttime', timestamp of the admission call
call_id VARCHAR(50), -- Replaces 'VocodeCallId', unique identifier for the call
call_outcome VARCHAR(50), -- Outcome of the call (e.g., 'Interested', 'Not Interested')
call_group VARCHAR(50), -- Group of calls (e.g., 'Initial Inquiry', 'Follow-up')
student_id VARCHAR(50), -- Replaces 'partyid', the ID of the student being contacted
event_type VARCHAR(50) -- Type of event or call (e.g., 'Admission Inquiry', 'Counseling')
);


--analyzing the relationships between admission call data and students' call outcomes,
--specifically examining the different outcomes for students who inquired about admissions.
--It attempts to explore patterns in call outcomes, by comparing each call’s outcome with all possible distinct outcomes that are not 'Not Interested'

INSERT INTO dim.student (student_id, active)
VALUES
('S001', TRUE),
('S002', TRUE),
('S003', FALSE),
('S004', TRUE),
('S005', FALSE);

INSERT INTO bi.admission_calls (call_time, call_id, call_outcome, call_group, student_id, event_type)
VALUES
('2024-10-10 09:30:00', 'C001', 'Interested', 'Initial Inquiry', 'S001', 'Admission Inquiry'),
('2024-10-10 11:00:00', 'C002', 'Not Interested', 'Initial Inquiry', 'S002', 'Admission Inquiry'),
('2024-10-11 14:00:00', 'C003', 'Interested', 'Follow-up', 'S001', 'Follow-up Call'),
('2024-10-12 10:00:00', 'C004', 'Interested', 'Initial Inquiry', 'S003', 'Admission Inquiry'),
('2024-10-12 15:30:00', 'C005', 'Not Responded', 'Follow-up', 'S004', 'Follow-up Call');

-----------------------CROSS JOIN---------------------------------
WITH distinct_call_outcomes AS (
SELECT DISTINCT call_outcome
FROM bi.admission_calls
WHERE call_outcome != 'Not Interested'
)
SELECT
ac.call_time AS call_date,
(ac.call_time::timestamp AT TIME ZONE 'UTC') AT TIME ZONE 'America/Los_Angeles' AS call_date_pst,
ac.call_id AS admission_call_id,
ac.call_outcome AS original_call_outcome,
outcomes.call_outcome AS call_outcome_all, -- Fetching distinct call outcomes for each row
ac.call_group AS call_group,
ac.student_id AS student_id,
s.active
FROM
bi.admission_calls ac
LEFT JOIN
dim.student s
ON CAST(s.student_id AS TEXT) = ac.student_id
CROSS JOIN
distinct_call_outcomes outcomes
WHERE
(ac.event_type = 'Admission Inquiry' OR ac.event_type = 'Follow-up Call')
AND ac.call_outcome != 'Not Interested'
ORDER BY
ac.call_time, outcomes.call_outcome;


-----------------------FULL OUTER JOIN----------------------------
SELECT a.* , b.call_outcome_all AS call_outcome
FROM (
SELECT
ac.call_time AS call_date,
ac.call_id AS admission_call_id,
ac.call_outcome AS original_call_outcome,
ac.call_group AS call_group,
ac.student_id AS student_id,
s.active
FROM
bi.admission_calls ac
LEFT JOIN
dim.student s
ON CAST(s.student_id AS TEXT) = ac.student_id
WHERE
call_outcome != 'Not Interested'
AND (ac.event_type = 'Admission Inquiry' OR ac.event_type = 'Follow-up Call')
ORDER BY
ac.call_time
) a
FULL OUTER JOIN (
SELECT *
FROM (
SELECT DISTINCT student_id
FROM bi.admission_calls
) a
CROSS JOIN (
SELECT *
FROM (
SELECT DISTINCT call_outcome AS call_outcome_all
FROM bi.admission_calls
WHERE call_outcome != 'Not Interested'
) b
) c
) b
ON a.student_id = b.student_id AND a.original_call_outcome = b.call_outcome_all;