Is there an idiomatic SQL (sqlite3) way of mapping a subset of selected records from a large table ("users") to unique values in a smaller table ("coupons")? It's easy enough to select all the user records not yet mapped, and to select all as-yet unmapped coupons, but then what's a nice clean way of mapping one to the other? Order doesn't matter, only that the mapping once established can be queried again in the future.
My brain is stuck thinking sequentially about this, frustratingly.
@glyph@jalefkowit it is not, there are a variety of other constraints applicable to user records in this case. (Sorry for being cagey, it's work stuff that I shouldn't disclose.) I have a sufficiently simple and performant query for this already which I could either use as a subquery for the insertion into the user→coupon table or as a CTE to accomplish the same thing, I'm just missing that last conceptual step. This isn't in my domain of expertise.
@gnomon@jalefkowit I couldn't quite golf it down to a single statement, since you cannot treat an 'insert returning' (or any DML) as a generalized source of rows or a subquery in SQLite, but as long as you have some arbitrary ordering you can zip arbitrary sets of rows together using window functions
@glyph@jalefkowit that is exactly it! Thank you so much for the functional solution but even more so for the terrific explanation!! I am gratefully in your debt.
@cks@glyph@jalefkowit update: the combination of CTEs and windowing functions did the trick. The underlying SELECT to pick out the applicable customer records takes about 30s - big data set, huge JOIN - but the user_id to unused coupon mapping adds about 200ms above that, about O(n) in the number of unmapped records, and then mostly because of the row writes.
I got exactly the solution I needed and I understand the underlying new-to-me concepts. Thank you again!!
@gnomon My intuition is that a truly stable mapping is impossible with purely read access; you have to write back some sort of thing to freeze it. I don't know if there's some clever way with left or right or outer/inner/upside down joins to get some sort of thing with unmapped users matched up to unmapped coupons, but it feels like probably not if there's not necessarily the same number of them on each side.
@cks in this case there is an expectation of the number of coupon codes being strictly larger than the number of users, by a safety margin designed to exceed the number of new users who could even in theory onboard during the validity period of the coupons.
I feel like if I could just order the users in a stable way, say by sequential record ID, then order the coupons by record ID, and filter out mapped pairs by NOT EXISTS in the user→coupon mapping table... Ugh I don't know, I'm a bad DBA
@gnomon
Put your mapping in a new user_coupon table with pairs of FKs to user and coupon? Won't affect either table, will be stable and mutatable, and you can put whatever uniqueness constraints you like in the new user_coupon table. Can even add attributes like use_by dates, etc. @cks
@hobs@cks absolutely, yeah. I have a schema and set of indices worked up already for that mapping table, including tracking redemptions, I'm just missing the proper concept for bulk mapping unused coupon codes to user records lacking an existing mapping.
@gnomon@hobs@cks conceptually when does it matter that a specific code is assigned to a specific user? (I’d guess when the user is actually informed of the code?)
If the user isn’t sent the code then consider when/how it will be used, how you are tracking that use, and when/how it matters to map to a specific user?
Related is this ask for a one off process (adding the mapping once but going forward it will be assigned as part of a process) or is it a process that will be repeated regularly?
@Rycaut@hobs@cks conceptually the mapping only needs to be materialized when the user is informed of it, yes.
The initial bulk mapping is intended to be a one-off, but the process will be repeated some as-yet undecided small number of times, and on each repetition there will be an additional mapping of all newly onboarded users since the last mapping, plus a (trivial, already implemented & tested) culling of redeemed coupons to avoid pestering users who have already taken action.
@gnomon@hobs@cks if you cull redeemed coupons you will need some way to know a user “had a coupon, used it, it was culled” (this might be tied to some concept like “user creation time” but feels like a lot potential area to watch out for.
Personally I’d be nervous about making it too complicated - if assign code & then inform user are two related processes (inform user perhaps waiting to send messages at the right time/means) can the bulk be done using the same code?
@Rycaut "cull" is perhaps too overloaded of a term. I just meant there is a column in the user→coupon mapping table that can be (bulk) updated to represent whether a code has been redeemed. Conceptually it can be a boolean, concretely it's a timestamp, in the current implementation it's just a IS NULL clause.
Assigning codes and informing users are definitely intended to be done as close together as possible to avoid missing user notifications, so a single transaction would be best, yes.
@gnomon sound reasonable. I’d probably look at how to make it one code that can be run against different sets of users (and this might also force the discussion of “how to bulk inform the users given codes in bulk” which seems like the next question)
@Rycaut fortunately that next step about the mechanics of alerting users is comprehensively solved and very well tested already, and the business has very conclusively decided that multiple user databases will not be an eventuality this team will need to care about.
(That's where most of the heavy lifting went over the last couple of years, which is why I now have the luxury of puzzling over a nicer, cleaner way of doing this one small thing instead of using our existing hack-job tooling.)
Add comment