Optimally different!

Last Friday I tweeted about the fact that I'd spent most of my workday designing a single SQL query. I'm pretty happy with what I ended up with, but while I'm good with SQL I'm not an expert by DBA standards. Then it occurred to me that, for once, nothing about what I was doing was proprietary or anything, and I should be able to freely share it, so, database folks, whatchathink of this...

Suppose you've got a table with a bunch of logs kept by a training content server (Moodle). Each log record has a timestamp and keys that reference tables with related information like user details, course details, etc.

You need to design a single MySQL query (multiple queries, temporary tables, etc are not an option) that gets the following...

• For each user who...
  • Has accessed a course in the last 30 days...
  • ...but only counting courses of a certain type (type information is in the related course record)
• Get aggregated for details from that subset of records for that user (total logged events, unique courses, unique dates, etc)
• AND certain non-aggregated details for the most recent event only from the subset for that user (date of visit, course visited, etc)
• BUT, there can be multiple records for the same user/timestamp combo, in which case you only want to produce one record, by just taking any one of the matching records for the non-aggregated details.

The combination of aggregated and non-aggregated fields in a single query, based on aggregated and non-aggregated criteria, and the fact that I couldn't count on having a unique key to work with proved particularly tricky.

( The solution I ended up with is under the cut-- I wonder if others can think of a better way... )