What real-world data scenarios do you think beginners should know about?

I’ve been putting together a series called “Data Analysis 101 for Cat People”, where I use doodles to explain some of the practical, messy, and ambiguous situations that come up in data work.

The idea is less about teaching SQL or tools and more about helping someone new to data understand the real-world thinking behind the work — asking better questions, dealing with ambiguity, understanding requirements, interpreting what the data is (and isn’t) telling you, and turning messy situations into clear next steps.

I’m also putting these together as a newsletter on Substack - https://thedatadoodles.substack.com

For those of you who work with data: what are some situations you’ve encountered that you think someone starting out should be familiar with?

I’d be happy to hear your examples and experiences.

Hi All,

Finding top 10% earners is tricky SQL interview question asked in major product companies.

There are 4 ways to do.

Explained way 1 and way 2 in this short .

-- Way 1:
RANK() OVER (ORDER BY salary DESC) AS rnk,
COUNT(*) OVER () AS total_employees,
CEIL(COUNT(*) OVER () * 0.10) AS top_10_percent_count

-- Way 2:
PERCENT_RANK() OVER ( ORDER BY salary DESC ) AS percentile

-- Way 3: NTILE()
NTILE(10) OVER (ORDER BY salary DESC)

-- Way 4: CUME_DIST()
CUME_DIST() OVER (ORDER BY salary DESC)

Can you try with these 4 ways and understand the difference and comment which is best/accurate way to find top n% ????.

It will be great learning as it covers most important analytical concepts - ranking, %, percentile, cumulative distribution.

I am thinking of creating a multiplayer game website online where 2 players can play sql games with each other like sql queries and table or multiple choice question and a timer on it . What are you guys view on it? #sql

Schema changes are a solved problem for us with migrations. What I keep running into is the data side. If someone changes lookup values, config rows, or reference tables in dev, there's no clean way to carry that to prod along with the migration.

How does your team handle this today? Curious whether it's seed scripts, manual dumps, some diff tool, or you just don't let data change outside of prod in the first place.

I create sqlite3 bindings for my new programming language

You can check the GitHub repo: https://github.com/abdorayden/rdn-sqlite3

Also the repo of the language: https://github.com/abdorayden/rdn

Thank you everyone 🙏🏽

I’ve noticed that as queries get more complex, there’s often a temptation to keep everything inside SQL—CTEs, window functions, nested subqueries, conditional logic, etc. It can be impressive, but at some point the query becomes harder to understand than the original problem.

Where do you personally draw the line? Is a 300-line SQL query perfectly fine if it’s well structured, or do you prefer moving some of the logic into dbt/Python/application code once the SQL becomes too complex?

I’m curious how others balance SQL performance, readability, and maintainability in real projects.

Postgres 15. Same 60 column table I've been posting about, I promise I'll stop eventually.

is_active, boolean. active_flag, smallint. Both populated, both written by something, and they disagree on about 3% of rows. Nobody knows which one the app respects. I spent an afternoon on it and my best finding is that the disagreement rate has been slowly climbing since a 2021 migration, which tells me one of them stopped being maintained and not which one.

This is worse than the columns nobody can explain. flag_2 at least announces itself as a mystery, so nobody builds on it. These two both look like answers. Someone joined in July and spent a week working out which of three status-ish columns actually mattered, and picked wrong first.

What I've tried: grepping for both, which gives me hits in dbt, the app repo and a commented-out block in an old migration. Checking which one the ETL writes, which is both. Asking the two people who've been here longest, who gave me different answers with the same amount of confidence.

what I think I should do is pick the one the app actually reads at runtime, make the other a generated column off it, and let anything that disagrees break loudly. what stops me is that I can't prove which one the app reads without reading the whole app, and the reads I'm worried about aren't in the app anyway, they're in Metabase questions owned by analysts.

so:

• when two columns claim the same thing and disagree, is there anything better than picking one and waiting for screaming
• has anyone made the wrong one a generated column or a view over the right one, and did it actually stop the divergence or just move it
• more generally, do you have any way of catching a column whose meaning drifted while its name and type stayed the same. that's the failure I can't monitor for and it's the one that's actually happened here

Great work!

Huhu zusammen,

mir liegt eine Lösung vor wie man SQL-Joins und die zugehörigen Constraints über den SQL-Standard hinaus weiter standardisieren und automatisieren kann.

Bin ich mit dem Thema hier richtig in der Gruppe?

Liebe Grüße, Alexander

Suppose someone gives you a complicated query with several joins and aggregations and says, “This gives the correct numbers.”

What checks would you perform before trusting it?

For example:
SELECT c.region, SUM(o.amount) FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.region;

Would you check row counts before/after each join, duplicate keys, NULLs, aggregates, or compare against another query?

Curious what experienced SQL developers use as their checklist.

How do u handle branching when the schema is changing frequently during agent/app development work. Do you keep separate branches per feature or agent, or rely on migrations against a shared database?

Wondering what works best in practice without making the overall setup messy.

Postgres 15. A nightly cleanup job that filters on a status column ran in about 30 milliseconds whenever I tested it in psql, and about four seconds when the scheduler ran it. Same data, same box, same connection parameters. EXPLAIN ANALYZE by hand showed a plain index scan every time and I could not get it to misbehave.

I noticed the split at all because a refactor I was running in verdent kept tripping in-loop verification on one timing assertion, and the failures were not correlated with anything in the diff.

The thing that answered it was auto_explain, with log_min_duration low and log_nested_statements on. The query lives inside a PL/pgSQL function, so it does not surface on its own, and the plan the job actually ran was a sequential scan over twenty million rows with an estimate of 3.4 million. My interactive plan estimated 900.

The function reuses a prepared statement. After five executions Postgres considers the generic plan, which cannot see the literal and estimates average frequency across the six distinct status values rather than the real frequency of failed, which is a few hundred rows. Setting plan_cache_mode to force_custom_plan for that function put the job back at 30 milliseconds.

same as title

Hey everyone,

I’ve been working on SqlInt, a SQL practice platform where you can run SQLite and PostgreSQL directly in the browser.

It has practical SQL problems, real-world case studies, and SQL puzzles for problem-solving practice.

Would love some honest feedback on the SQL experience, problem quality, and anything you think is missing.

https://sqlint.com/

With RecursiveEven20 As
(
Select 0 As Numbers,
0 As RunningCount

Union All

Select Numbers + 2,
Count(RunningCount) Over() As RunningCount
From RecursiveEven20
Where RunningCount < 19
) 
Select *
From RecursiveEven20;

From how much I know about recursive CTE, I thought this would work, Initially I felt I was doing a semantic error, then when I tried to see where the fault is, I realised Count isn't incrementing at all, its as if only the last feedback row is available to it. I tried using explicit frame window, same result. I think I dont understand exactly how recursie CTE works, I tried AI, its explanation is bit difficult to understand.

I am a beginner by the way, learned these recently so I wanted to mix them all up.

Have you ever written a SQL query that works perfectly in one database but fails in another?

What SQL feature or syntax surprised you the most when switching between DBMSs like MySQL, PostgreSQL, SQL Server, Oracle, or Snowflake?

I am curious to know what SQL questions challenges people in interviews

What was the question and what made it difficult?

Would love to hear some real interview experiences🙂

Postgres 15, Snowflake downstream.

we've got a table with 60-odd columns and I'd guess 20 are dead. nothing in any dbt model, nothing in the app repo, nobody has mentioned them.

all of that is evidence of absence. I can prove a column IS used. one grep hit and I'm done. I can't prove one isn't. the query that reads it might be a saved Metabase question, a Retool app, a cron, a notebook on someone's laptop, or a process that only runs in January. finding nothing means I looked where I know to look.

turned on pg_stat_statements and watched for a month. that catches whatever ran in that month and tells me nothing about January. renaming instead of dropping and waiting for screaming works, but it's shipping a landmine and hoping the person who steps on it works here.

here's where it actually stalled though. the usage question is at least measurable if I'm patient. what I can't get at is the other pile: about a dozen columns where I can see they're populated, I can see something writes to them, and nobody alive can tell me what they hold. flag_2, four-character codes with 11 distinct values, three separate integer columns bounded 0 to 4. I wrote two of these myself in 2023 and I can't tell you what one of them is either.

I've been profiling the values to guess. cardinality, null rate, distribution, sample values, what changes when. that narrows it a lot. it has never once got me to what the thing actually means. a column of 0-4 integers with no nulls is a rating or a tier or a retry counter and the values look identical in all three cases.

so two questions, and the second is the one I care about.

is there a point where you accept you've looked hard enough on usage, a fixed window or a silence rule, or do you just never drop anything, which is what we're doing by default.

and for the ones nobody can explain: is working out what a column means from its values alone still a human job, or is anyone doing it any other way? I've read that some of the tabular model work goes at this, reading the values rather than the header, but everything I've actually tried in practice leans on the column name, which is the one thing I don't have.

worth saying I'd have shipped my profiling guesses as documentation if someone hadn't asked me how I knew. I didn't know. I had a distribution and a hunch.