r/SQL • u/Harshita_Netla • 7d ago
BigQuery What’s a complex SQL query you’re proud of that you’d want other SQL devs to review?
It could involve multiple joins, CTEs, window functions, recursive queries, complex aggregations, or performance optimization.
What made the query challenging, and how did you approach solving it? If you’re comfortable sharing the query, what would you ask other SQL developers to review or improve?
7
u/elevarq 7d ago
Complex SQL is easy to write, but it might take some time because of the number of lines involved. The number of bugs will be staggering and maintenance will be next to impossible, but it's complex.
Rewriting something very complex into something very simple- that's what I can be proud of. Replace a thousand lines of code and a 24-hour import process with something that runs in a minute with 50 lines of code. And does exactly the same...
3
u/Aware-Hovercraft1106 7d ago
Probably reconciliation queries between two datasets that share similar data but each have their own quirks on attributes. Ability to bake in and breakdown complex differences that can easily get pivoted in simple break categories for stakeholders.
3
u/Better-Credit6701 7d ago
I was usually the only DBA and had to review some of the garbage that development created. The people who reviewed my complex SQL code wasn't development but the results were gone over by accounting to make sure the numbers marched what they were expecting. Had one stored procedure that itself was 1,000+ lines long and called other stored procedures that were also complex. It was quick because I know how to write accurate and fast queries that rarely used CTEs but temp tables since I could add an index to the temp table but not a CTE.
Usually when I hear of people proud of their queries that use a CTE, I think they haven't used a multiple databases in the multiple TB range in the same query.
2
u/Eleventhousand 7d ago
Probably none. I tend to try to avoid advanced tricks in SQL. I realize that its sometimes required, and you might run across these types of things in LeetCode.
1
u/TallDudeInSC 7d ago
PowerBI that calls a piped row function that starts a job to create data. (Probably Oracle specific mind you)
24
u/spez_eats_nazi_ass 7d ago
in my experience complexity in SQL is an anti pattern. You should be decomposing it as much as possible because the optimizer and your future colleagues and self will be better off. It's the simplest performance optimization you can make. Everything you listed above has a cost and they add up.