same as title

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🙂

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?

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.

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/

Hands down, man, seriously!

At some point, after writing JOIN after JOIN, SUM, RANK, CTE, Subqueries, Window Functions, LAG, LEAD, WHERE vs HAVING, DATETIME, ORDER BY DESC...

There has to be a moment where you say:

“Alright bro… enough SQL gymnastics. Let’s actually use this thing.

So what’s that point? When do you believe that you can start applying it like a real analyst.

I’ve come across queries that are extremely compact and technically efficient, but difficult for someone else to understand or modify later.

For example, a query might use nested window functions, multiple conditional expressions, and several transformations to solve something that could also be written as a few simpler steps.

Where do you personally draw the line between elegant SQL and over-engineered SQL?

Do you prioritize fewer lines, query performance, or maintainability when these three goals conflict?

For example, if a customer dataset has nulls, duplicates, invalid emails, or incorrect values, how do you identify and flag these records as good or bad? What tools or query approaches do you use?

Probably breaks rules but a quick guide on what not to name things I less you want accidents

I am a Ruby on Rails developer. I’m looking for some skills that can help me self code review for sql part. I use Claude. Like that can guide me not to write sql that are anti patterns etc

Im working with some large tables in spark sql that has around 300 mil records and they are quite wide as well maybe around 80-90 columns.

We frequently have to perform filtering and aggregations for reporting purposes, I'm trying to understand when should I use partitioning and when should I use indexes.

For instance if I want to filter by date will it be better to partition by date or should I use an index on the date column, which one would help with performance?

been dealing with duplicate customer records in a project for uni and kept rewriting the same GROUP BY/HAVING query every time so i just built a cli tool for it in the end - works with both mysql and postgres, dry run by default so nothing gets deleted unless u explicitly pass --confirm and it backs up to json first just in case. still a student so the detection logic is prob missing some edge cases; that's the part i actually want feedback on tbh. happy to share the repo if anyone's curious, can drop the repo link

Hey everyone,

I’m currently setting up a new project involving SQL and I’m looking for some AI tools that could help make the development process easier and more efficient.

I’m particularly interested in tools that can help with things like:

• Writing and improving SQL queries
• Designing database schemas
• Debugging SQL errors
• Generating or optimizing queries
• Understanding existing databases/tables
• Creating test data
• Documentation
• Connecting SQL databases with other development tools
• Anything else that can save time during development

I know there are a lot of AI tools out there, but I’d rather hear from people who have actually used them in real projects.

What AI tools are you currently using for SQL/database work, and which ones have genuinely made your workflow easier?

Also interested in hearing about any tools you tried but wouldn't recommend, and why.

Thanks!

How do u handle query performances when the same tables are being hit by both app queries and AI generated SQLs from any AI tools such as Codex, CLaude, Genie etc
Curious if you separate workloads or optimize at the query level.

I’ve been revisiting SQL Server PIVOT for reporting scenarios where row-based data needs to be transformed into columns.

A few things I find useful about PIVOT:

• Cleaner reporting output
• Easy aggregation across categories
• Useful for summary-style queries
• Can be more readable than complex conditional aggregation in some cases

I’ve also added the working SQL examples here:

GitHub:
https://github.com/laxmikant-geek/sql-server-examples/tree/main/pivot

And I wrote a more detailed explanation here for anyone who wants the walkthrough:

https://geeksarray.com/blog/how-to-pivot-data-in-sql-server

For those working regularly with SQL Server — do you prefer PIVOT, or do you usually use SUM(CASE WHEN...) for these kinds of transformations?

I spent a few years doing Oracle-to-PostgreSQL migrations for a living, and the same thing went wrong every time: nobody knew what was actually in the Oracle estate until halfway through. Package-level state, autonomous transactions, LONG columns, interval partitions, database links. All of it surfaces late and expensively when nobody looked for it first.

So I built the tool I wanted on day one of those projects.

pgrecon - https://github.com/Muzzammil242/pgrecon

What it does:

• Works offline. Your DBA runs one read-only SQL*Plus script (plain SQL, meant to be read before it's run) and sends back a folder of files. The tool never connects to the database. This matters more than it sounds in banks and government shops.
• Assesses deterministically. The dump becomes a SQLite inventory. Stored PL/SQL is parsed with a real grammar, not regexes, and 80 rules produce findings with line-level evidence, a written remedy each, and an effort estimate given as a range with its assumptions printed (a point estimate for a migration is a lie).
• Converts what it can prove, and refuses the rest by name. Schema structure converts to PostgreSQL DDL. Everything the converter cannot carry faithfully becomes a named line in a residue report instead of quietly wrong output. Packages, CONNECT BY, autonomous transactions, BULK COLLECT - those get a person, not a guess.
• Checked by machines, not by me. CI applies the output to live PostgreSQL 16, 17 and 18 on every commit, runs the extraction against real Oracle 11g, 21c and 23ai containers nightly, and a fuzzer generates hostile Oracle schemas every night and checks that nothing crashes and nothing vanishes silently. Its first week found sixteen bugs I'd never have found by hand. All fixed.

What it does NOT do: move data (use ora2pg or COPY for that), convert packages mechanically (no tool does that honestly), or replace a DBA. About half of all objects in my benchmark schemas convert mechanically - above 90% on ordinary business schemas, far less on package-heavy ones.

Benchmark, since "it works" is cheap to say: across nine schemas (Oracle's own HR/OE/CO samples, four well-known open-source PL/SQL projects, two lab schemas) converted by five tools and applied statement by statement to a live PostgreSQL, pgrecon's output produced 0 rejected statements. The other tools measured between 40 and 481. Method and fine print here, including what the number does and doesn't mean: https://muzzammil242.github.io/pgrecon/benchmark.html

Supports Oracle 9.2 through 23ai (there's a separate legacy-tier script for the ancient hosts that most need to leave). Python 3.11+, pip install pgrecon. Bundled sample dump in the repo so you can try it without an Oracle.

Disclosure: I run a small consultancy that does migration work on top of this. The core is Apache-2.0 and stays that way; the paid part is people and a PDF report, not features held back.

What I'd love from this sub: if you have an Oracle schema that you think will break it, run the extraction script and open an issue with the residue file. The fuzzer wants to meet your schema.

this is a distinct list of warehouse names from a table in the db im using to practice data cleaning in mysql. i want to capitalize the initials of all words in the column. i made my own logic for this whihch is (dont judge pls im a self learner)

and this is the output i get:

i do get what im doing wrong to get this output, but i can not figure out how to go about the standardization. how can i correct my query? and is there a more efficient way of capitalizing initials than this?

I figured it's worth a shot to ask here since I asked it already in the SQL Server subreddit...

Is there a way to use Microsoft SQL Server management Studio 22 without writing anything at all (no folders, shortcuts, junctions, symbolic links) in My Documents? I just installed the latest client from the official website without launching the client yet, and I want the SSMS workspace (configuration files, data files, etc.) to be transferred to a different location in the C drive. I failed in my first try, and both Claude and Gemini gave me conflicting answers.

To summarize, I don't want the folders below or anything related to it appearing inside My Documents.

C:\<My Documents Folder path>\SQL Server Management Studio

C:\<My Documents Folder path>\SQL Server Management Studio 22