APEXia.world - The Raffle Query

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.

1. There are 5-6 cards in a realm (aka theme).

2. Each card has one (1) associated combination card, except the "Insum Citadel of APEX Mastery" can combine with any card.

3. Having multiples of the same card does not help in any way.

4. You earn one raffle ticket for each realm in which you have 5 cards.

5. You earn one raffle ticket for every two pairs of combination cards.

   1. The "Insum Citadel" can only be used to match one card for this purpose.

   2. The "Insum Citadel" can not be used to match with one that is already one of your matches.

   3. 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;