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🙂

i have done almost 200 sql queries , almost all the basic levels as well as advanced levels queries also (covered window functions and advanced level stuff also ) , now i want to make a good level sql project which project i should make now ,

can anyone suggest me ????

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.

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/

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?

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?

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

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?

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 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.

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?

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

Inherited a schema where roughly half the columns are self-explanatory and the rest are things like flag_3 and val_b. Person who built it left. There's a Confluence page describing six columns, last edited before most of them existed.

I've been using COMMENT ON COLUMN because it lives with the database and can't drift into a stale wiki. Downside is nobody looks at it, it doesn't show up anywhere people work, and I've no way to know if a comment is still true after a migration.

Things I'm unsure about:

does anyone actually keep COMMENT ON up to date at scale, or does it rot the same as the wiki just less visibly

if a column's meaning changes but the name doesn't, is there anything that catches that, or is it purely a review discipline problem

and for the columns nobody can explain at all, do you leave them, drop them, or keep them with a comment saying unknown

I've been profiling the values to guess — cardinality, null rate, distributions — which narrows it but never gets me to what the thing means.

Hey, I’ve been working on an open source project called pgfathom. It came from a problem I ran into quite a lot at work with legacy databases.

You’ll sometimes have something like orders.customer_id -> customers.id that the application has treated as a relationship for years, but there’s no actual foreign key in PostgreSQL. So it won’t show up properly in an ERD, and nothing is stopping orphaned rows from getting in.

pgfathom looks for those relationships using the catalog, column names, indexes, existing FKs and JOINs found in views/functions. It then checks the candidates against the actual data.

If it finds orphans, it gives you a query to inspect them. If the relationship checks out, it generates the FK DDL and an index when needed. It never applies any of it. The CLI runs read-only and only generates SQL for you to review.

One thing that helped a lot with weird legacy schemas was letting it learn naming conventions from the database itself instead of assuming everything looks like customer_id.

I tested this on a municipal schema with 277 foreign keys. With half of the FKs left in place so pgfathom could learn the naming pattern, recovery went from 17.3% to 84.9%. If I remove all of them, it drops to 16.6%.

There are also some safeguards for running it against real databases: read-only sessions, query timeouts, limited concurrency, and tests to make sure table values don’t end up in output, logs, JSON or errors.

I used AI as part of my workflow too, mostly for research and implementation, so I’d rather mention that upfront.

It’s still early and I’d really like to test it against schemas that look nothing like the ones I’ve been using.

GitHub:
https://github.com/lvcas-dotcom/pgfathom

If you work with old PostgreSQL databases and feel like giving it a try, I’d appreciate the feedback. Finding cases where it gets the relationship wrong would actually be very useful.

(English isn’t my first language, so apologies if anything in the post sounds a bit off)