by 
Unearthing Joins That Cause a Stir!
We’ve all encountered that pesky join that unexpectedly multiplies or just runs amok. Previously, we’d delve deep into our datasets, trying to pinpoint the culprit. Now, let’s devise a function to streamline this detective work.
Your challenge this week: Design a function that takes a query ID and pinpoints any joins within it that produce more rows than the anticipated 1:1 ratio. To put it simply, if a join merges 10 rows from its left and 80 from its right, yet results in more than 80 rows, your function should highlight such discrepancies.
Start-up
--Schema creation
create or replace schema week_63;
--T1 creation
CREATE TABLE t1 (value CHAR(1));
INSERT INTO t1
SELECT 'a' FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) AS temp1
UNION ALL
SELECT 'b' FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20) AS temp2
UNION ALL
SELECT 'c' FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30) AS temp3
UNION ALL
SELECT 'd' FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20) AS temp4
;
-- Create table t2 and insert one 'b' value
CREATE TABLE t2 (value CHAR(1));
INSERT INTO t2 (value) VALUES ('b');
-- Create table t3 and insert ten 'c' values
CREATE TABLE t3 (value CHAR(1));
INSERT INTO t3
SELECT 'c' FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) AS temp;
-- Create table t4 and insert one 'd' value
CREATE TABLE t4 (value CHAR(1));
INSERT INTO t4 (value) VALUES ('d');The query we’d like you to check :
Query
SELECT *
FROM t1
LEFT JOIN t2 ON t1.value = t2.value
LEFT JOIN t3 ON t1.value = t3.value
LEFT JOIN t4 ON t1.value = t4.value;The result of your function should be similar to this :
Good luck!
7 responses to “Week 63 – Hard”
-
Thanks for this challenge, very useful!
I may have messed something up somewhere, as my “row_multiplier” is slightly different- Solution URL – https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_63_03.sql
-
My row multiplier was also different. I got 3.888889
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
-
I realised where I went wrong and updated my answer. I now get the expected 4.375
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
-
Thanks for continuing to highlight interesting new functionality!
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
-
I realised where I went wrong and updated my answer. I now get the expected 4.375
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
-
Also got caught on my multiplier being incorrect so had to go back and add in some additional logic to get the correct values for the calculation
- Solution URL – https://github.com/ChrisBo94/FrostyFriday/blob/main/Week_63.sql
-
A very useful one! I didn’t know about the “get_query_operator_stats” function
- Solution URL – https://github.com/marco-scatassi-nimbus/Frosty-Friday/tree/main/week63
Leave a Reply
You must be logged in to post a comment.