slopfront
Reddit, read-only. No JavaScript. No cookies. No tracking.
preferences

Practicing Complex Queries and Large Datasets

15 points · submitted 2d ago by u/leogodin217

A while back, I asked what's missing in this sub. Though not a long discussion, one thing stood out. Working with long, complex queries is something we all run into eventually, but have to learn through experience. I don't think there are a lot of resources for practicing this stuff.

Fabulexa Complex Queries is my first attempt at providing practice. These are intermediate, and possibly advanced, exercises on complex queries within a full data warehouse. Specific exercises have two different tracks.

  1. Revenge of the 1998 PERL developer: Complex billing logic for a SaaS company written by someone who believed keystrokes should be minimized and readability is an anti-pattern. Can you take the most obtuse, bad-practice-filled query I've ever seen and refactor it? Make it readable.
  2. Bug hunting in a well-written query. Can you fix the bugs described in tickets? Does it get easier as you learn the data and the data-warehouse structure.

If you are looking for more difficult practice that mimics the real world, this might be for you. If you write, teach or create courses, this might be useful as well. Feel free to grab the data and do your own thing. Hopefully, I'll do a series on this soon.

Finally, suggestions, PRs and comments are very much appreciated.

5 comments · 100% upvoted · other discussions

comments

RomirBuilds 1 points · 2d ago

Looking at the NHS spec and check-pack.sh, one useful addition could be an optional diff summary for exercises whose output schema and grain stay the same.

The contract has a unique (month, specialty, metric) key, but the grader currently prints missing/unexpected whole-row strings. Could a diagnostic view pair rows by that key and show which of numerator, denominator, value or target changed, with expected and actual values side by side? A single changed value currently appears as both a missing and an unexpected row.

Keep the EXCEPT ALL comparison as the grading verdict, and retain the raw diff for duplicates, schema changes and the grain-change exercise. The summary would help learners find the affected metric family without revealing the faulty query line or the solution.

AI-assisted.

leogodin217 [OP] 1 points · 1d ago

I'm a little torn on this. On one hand, the UX could be improved a lot. On the other hand, doing this type of stuff is useful practice and learning on its own.

RomirBuilds 1 points · 2h ago

That makes sense, especially for the intermediate audience you're aiming at. Building the comparison could itself be an optional exercise: take the raw diff, identify the affected keys, and produce a report of which fields changed.

I'd keep the raw output as the default and offer a worked comparison query as a separate hint after an attempt. That would preserve the debugging practice while giving stuck learners a way forward. It also makes the tradeoff explicit: learning to build diagnostic tools is part of the exercise.

AI-assisted.

Previous_Version5651 1 points · 2d ago

The "refactor the terrible query" track is the right instinct. One drill that helped me more than any course: take one messy query and rebuild it as a chain of CTEs, each CTE owning exactly one business rule, and after each step run a count(*) plus one known total to prove the row count and the money still match. You end up with a query a reviewer can audit line by line, and you learn where the original silently dropped rows (usually a LEFT JOIN that became an INNER JOIN in WHERE). Doing that on someone else's bad query is better practice than writing greenfield SQL, because greenfield never teaches you to read code you did not write.

leogodin217 [OP] 1 points · 1d ago

Yeah, I hope to make a video about exactly this. My process is basically that, break up the query into CTEs, add good table/column aliases, then start looking at the logic in pieces.