At KScope23 my claim that I wrote a query in 5 minutes was exposed--even questioned. I'll admit it here. I did not write the query in 5 minutes. Of course, parsing just what "write the query" means is part of the question. By the time I sat down to start typing out the query I had already spent a lot of time thinking about it. I thought about it during dinner, while out biking, and even in my sleep. Over the course of 30 years of writing queries, I've also written a few with a lot of similarities to this business requirement. So, by the time I sat down to start typing I had years of time put into this query and it was fully formed, kicking around inside my skull. Still, it probably took me longer than 5 minutes just to move it from my skull to the database.
The rules are as follows.
There are 5-6 cards in a realm (aka theme).
Each card has one (1) associated combination card, except the "Insum Citadel of APEX Mastery" can combine with any card.
Having multiples of the same card does not help in any way.
You earn one raffle ticket for each realm in which you have 5 cards.
You earn one raffle ticket for every two pairs of combination cards.
The "Insum Citadel" can only be used to match one card for this purpose.
The "Insum Citadel" can not be used to match with one that is already one of your matches.
This means you must have a minimum of 4 cards to have 2 pairs.
Below is the query. It relies on some views, but a careful reader can probably figure out what is going on. I've added a few comments to help clarify.
I probably could have used a model clause or some other modern SQL technique, but I didn't. I didn't anticipate a huge data set, so I didn't spend time working on performance. It's super fast with the data gathered at KScope, but with that small data set it also does a couple of full table scans--no problem for small data sets, and the optimizer might choose something different with a large data set. I'll have to test that in development some day. For now, though, this is the query that determines if and how many raffle tickets each person has.
with combos as -- card with combination card or card with citadel (select -- the row_number per user is used later to create pairs of matches in "doubles" row_number() over (partition by ucv1.user_id order by ucv1.card_id) rn, ucv1.user_id, ucv1.user_email, ucv1.user_full_name, ucv1.user_picture, ucv1.card_name, case when ucv1.user_has_combination_card = 'Y' then ucv1.combines_with_name else 'Insum Citadel of APEX Mastery' end combined_with from xia_user_card_v ucv1 where ((ucv1.user_has_combination_card = 'Y' and ucv1.card_id < ucv1.combines_with_id) or (ucv1.user_card_id = (select min(no_match.user_card_id) -- allow user to match one card without a match with the citadel from xia_user_card_v no_match where no_match.user_id = ucv1.user_id and no_match.user_has_combination_card = 'N' and no_match.combines_with_id is not null) and exists (select 1 from xia_user_card_v citadel where citadel.user_id = ucv1.user_id and citadel.combines_with_id is null) ) ) ), doubles as -- two sets of combos for the same user (select 'Combo Cards' reason, c1.user_id, c1.user_email, c1.user_full_name, c1.user_picture, c1.card_name ||' + '|| c1.combined_with ||', ' || c2.card_name ||' + '|| c2.combined_with reason_detail from combos c1 join combos c2 on c2.user_id = c1.user_id and c2.rn = c1.rn + 1 -- get a unique second pair where mod(c1.rn,2) = 1), -- get half the rows realms as ( -- n cards in a theme select 'Realm' reason, xu.user_id, xu.email user_email, xu.first_name ||' '|| last_name user_full_name, xu.picture user_picture, xt.name reason_detail from xia_user xu cross join xia_theme xt where 5 <= (select count(*) from xia_user_card_v ucv2 where ucv2.theme_id = xt.theme_id and ucv2.user_id = xu.user_id) ), the_list as (select * from doubles union all select * from realms) select the_list."REASON",the_list."USER_ID",the_list."USER_EMAIL",the_list."USER_FULL_NAME",the_list."USER_PICTURE",the_list."REASON_DETAIL" from the_list;