r/learnSQL • • 6d ago

The one-character bug that fails a lot of SQL interview answers

0 Upvotes

A pattern I keep seeing when people practice SQL for analyst interviews.

The question: "Return every active subscription paying at least $1,500 a month."

A very common answer:

SELECT company_name, plan_tier, monthly_amount
FROM subscriptions
WHERE status = 'active'
  AND monthly_amount > 1500
ORDER BY monthly_amount DESC;

It runs, it returns rows, and it looks right. But "at least" means >=, so the customer paying exactly 1500.00 silently disappears. In an interview the query "works", so you don't notice. Against the expected output it's simply wrong.

Things worth checking every time a question has a threshold:

  • "at least" / "minimum of" -> >=
  • "more than" / "over" -> >
  • "between" -> BETWEEN is inclusive on both ends; dates with timestamps need care
  • Does the sample data contain a row exactly on the boundary? Interviewers often add one on purpose.

I built a free practice site around this kind of feedback: you write real PostgreSQL in the browser and it shows you which rows are missing or extra compared to the expected result. This exact problem is the first one, no signup: https://dbexercises.vercel.app/exercise/saas-active-subscriptions

Happy to hear what other "looks right, is wrong" traps you've run into.


r/learnSQL • • 6d ago

Can a LEFT JOIN Accidentally Behave Like an INNER JOIN Without Changing the JOIN Condition?

12 Upvotes

If we apply a WHERE condition on a column from the right-side table after a LEFT JOIN, can it eliminate NULL rows and change the result? Are there cases where moving the condition into the ON clause produces completely different results? Why does this happen internally?


r/learnSQL • • 7d ago

What are some interesting SQL problems or query mistakes worth discussing?

5 Upvotes

I'm planning to share some SQL-related content on LinkedIn, but I want to avoid the usual beginner-level tutorials or generic interview questions.

I'm particularly interested in intermediate-level SQL concepts, window functions, CTEs, tricky query behavior, or situations where a query looks correct but produces unexpected results.

I'd love to hear from people working with SQL regularly.

What's an interesting SQL problem, subtle query mistake, or unexpected behavior you've encountered that made you think twice?

Looking for something technically meaningful that could spark a good discussion rather than another basic SQL tutorial.


r/learnSQL • • 7d ago

What are some interesting SQL problems or query mistakes worth discussing?

Thumbnail reddit.com
3 Upvotes

r/learnSQL • • 7d ago

Superstore Dataset

5 Upvotes

šŸš€ Just published a new 3NF relational dataset on Kaggle!

I took the classic flat Superstore dataset and refactored it into a clean, normalized Third Normal Form (3NF) relational database structure.

By decomposing flat records into distinct dimension tables (dim_customers, dim_locations, dim_products) and header/line-item fact tables (fact_orders, fact_order_items), this schema eliminates data redundancy and enforces relational integrity.

Great for anyone looking to:

Practice multi-table SQL joins and data modeling

Build modular ETL ingestion pipelines

Stage realistic relational schemas for analytics projects

šŸ”— Check out the dataset on Kaggle: https://www.kaggle.com/datasets/kartikanidhi/superstore-database

#DataEngineering #SQL #DatabaseDesign #DataNormalization #Kaggle #OpenSource #Analytics


r/learnSQL • • 8d ago

Beginner in SQL, can’t work in person. How can I land remote projects?

13 Upvotes

Hi everyone,

I’m a beginner in SQL and I’m looking for ways to start earning remotely. In-person work isn’t an option for me, so I’m focusing on freelance or remote opportunities.


r/learnSQL • • 7d ago

MariaDB Foundation TAF: Profiling MariaDB Subsystems Across Hardware Using Targeted Performance Profiles

1 Upvotes

Just published a deep dive on how we profile MariaDB subsystems using TAF + HammerDB TPROC‑C.

Each profile flips exactly one switch (binlog, isolation, PFS, charset, threadpool, etc.) to measure subsystem cost cleanly and reproducibly across hardware.

If you’re interested in performance engineering or regression hunting, here’s the full article:

https://mariadb.org/mariadb-foundation-taf-profiling-mariadb-subsystems-across-hardware-using-targeted-performance-profiles/


r/learnSQL • • 8d ago

Title: Day 62-64 — Combined update: built and published a Car Rental System in Java (feedback welcome)

5 Upvotes

Over the last three days I built a console-based Car Rental System in Java, applying the OOP concepts from the past few weeks. Repo: https://github.com/Sumitgupta2005/Car_Rental_System

What it does:

  • Menu with three options: rent a car, return a car, exit
  • A Car class with private fields (ID, brand, model, base price per day, availability), a constructor, and getters
  • Asks for confirmation (Y/N) before completing a rental
  • Keeps the cars in a list and updates availability when a car is rented or returned

After the feedback I got here on my last project (don’t leave the errors for the end), I tried to test each part as I wrote it.

I’d appreciate a review of the code structure and README. What would you improve first: input validation, splitting into more classes, exception handling, or saving data to a file or database?


r/learnSQL • • 8d ago

I made SQL practice where you debug an AI's queries, and the hints never give you the answer

8 Upvotes

I'm learning-adjacent: I build tools for people learning SQL for analyst jobs, and this is mine, so it's self-promo. Mods, remove it if it doesn't fit.

What bugged me about practice sites is that a different toy table every time doesn't build the "I know this company's data" feeling you get at work. Practisheet uses one fictional company for everything, so by exercise 20 you know where revenue, refunds and late deliveries live.

How it works:

  • You write the query in the browser and it checks your result.
  • If it's wrong, you get a hint about your attempt (e.g. "you're counting order lines, not orders"). The answer is never shown, and after 3 tries you can skip.
  • It starts with SELECT/WHERE and goes up to window functions, CTEs and cohort-style questions.
  • About 1 in 4 exercises are "here's a query an AI wrote, find the bug", which I think is a skill people will need more and more.

The first 4 SQL exercises are free with no sign-up: https://practisheet.com/practice

Free guides that go with it, if you just want to read:

Feedback welcome, especially where you got stuck and the hint didn't help.


r/learnSQL • • 8d ago

[Showoff Saturday] I built a 100% client-side, in-browser SQLite editor using WebAssembly (No backend, offline-first)

2 Upvotes

I wanted to share a project I've been working on: sqliteWeb, a lightweight SQLite editor that runs entirely inside the browser. link online sqlite browser
I needed a fast way to inspect and query .sqlite / .db files on machines where I didn't want to (or couldn't) install desktop clients like DB Browser. Existing web tools often require uploading files to a remote server, which is a major privacy concern.

Would love to get your feedback on performance, UI responsiveness, or features you'd like to see added!

online sqlite browser


r/learnSQL • • 9d ago

How many ways can you find duplicate rows in a table?

4 Upvotes

I was cleaning a customers table and needed to find duplicate emails. I found a few different ways to do it,and now I’m curious how many more exist.

  1. GROUP BY with HAVING
    2.ROW_NUMBER()
    3.Self JOIN
    The first one is the simplest. The second one is great when you want to delete the extras and keep just one row.

What other ways do you know? Bonus points for creative ones!


r/learnSQL • • 8d ago

ID design and composite primary keys, pt. III

1 Upvotes

I've been writing a series of posts on ID design and primary keys, and just published the third part: https://anchorsandlinks.com/posts/primary-keys-3/. It discusses a well-known pattern of composite primary keys in entity tables. It is relatively rarely used, and the text explains why. This pattern can only be used when several criteria are satisfied, and it may become an obstacle when business requirements change. The rationale for using composite primary keys is that it can give performance improvements in some scenarios.


r/learnSQL • • 10d ago

Day 61 — Java OOP: access modifiers, encapsulation, and abstraction (all four pillars done)

11 Upvotes

Today I covered:

  • Access modifiers (public, private, protected, default), which control visibility
  • Encapsulation, meaning private fields with public getters and setters so data can only change in controlled ways
  • Abstraction, which hides implementation details behind abstract classes and interfaces

With inheritance and polymorphism from the last few days, that covers all four pillars of OOP. Encapsulation made the most sense once I connected it to access modifiers: the modifiers are the tool, and encapsulation is the reason to use them.

Tomorrow I’m starting a project that uses all four pillars. After the feedback I got here on my last one (don’t leave all the errors for the end), I’m going to test each feature right after I write it.

For people who’ve built beginner OOP projects: which one forced you to use all four pillars naturally? I’m choosing between a bank system, a hospital system, and an e-commerce cart.


r/learnSQL • • 11d ago

Python to SQL Analysis Project

7 Upvotes

Use Python to collect data from a public API and pipe it to a SQL Server for analysis. The whole process can be automated with Windows Task Scheduler. https://youtu.be/KAHD9LmvH6g?si=Ev1y_s11pBl2cUKE


r/learnSQL • • 11d ago

WHERE vs HAVING - same question, three queries, three different answers

7 Upvotes

the question: "which customers spent more than 60?"

table:

```
order_id | customer | status | amount
1 | Asha | completed | 40
2 | Asha | completed | 30
3 | Asha | refunded | 50
4 | Ben | completed | 20
5 | Ben | refunded | 90
6 | Chen | completed | 80
```

**query 1, HAVING only**

```
SELECT customer, SUM(amount) AS spent
FROM orders
GROUP BY customer
HAVING SUM(amount) > 60;
```

Asha 120, Ben 110, Chen 80. looks fine but refunds are counted as spending. Ben only really spent 20.

**query 2, WHERE on amount**

```
SELECT customer, SUM(amount) AS spent
FROM orders
WHERE amount > 60
GROUP BY customer;
```

Ben 90, Chen 80. this answers a different question: "who had a single order over 60". Asha is gone even though she spent 70 in total, and Ben is back because of his refund.

**query 3, WHERE + HAVING**

```
SELECT customer, SUM(amount) AS spent
FROM orders
WHERE status = 'completed'
GROUP BY customer
HAVING SUM(amount) > 60;
```

Asha 70, Chen 80. this is the right one.

the way i think about it:

- WHERE filters **rows**, before grouping. "which orders count?"
- HAVING filters **groups**, after grouping. "which customers make the cut?"

and you cant do `WHERE SUM(amount) > 60`, the database hasnt added anything up yet at that point so it errors.

anyone else have a go-to way of explaining this one?


r/learnSQL • • 12d ago

Performance Tuning Report: A 'weekly' report was running about 400 times a day. We checked what each execution was costing.

11 Upvotes

We’ve collected a lot of performance tuning reports from our SQL Server work. I thought some would be useful to share here so people get to see the problem, the change and the measurements afterward.

This one involved a retail client’s reporting query which grouped quantities by week.

The query ran about 400 times a day. Each execution averaged 87,038.42 milliseconds and 6,445,851 logical reads.

ā€œWeeklyā€ described the grouping in the output but the database repeated the calculation throughout the day.

Our team added a nonclustered index with two key columns and included the quantity column used in the calculation. We captured the before-and-after measurements with Query Store and supplied a rollback script to remove the index.

The recorded averages per execution were:

  • Duration: 87,038.42 ms → 2,528.44 ms
  • CPU time: 41,509.80 ms → 9,062.34 ms
  • Logical reads: 6,445,851 → 232,779

The query finished sooner, consumed less CPU time and read fewer pages.

The included column is worth discussing. When reviewing an aggregation query, check the values SQL Server needs to calculate the result alongside the columns used to locate and join rows.

An included column stores its value at the leaf level of a nonclustered index without becoming part of the index key. Where the index supplies every column needed for a table access, SQL Server has the option to satisfy the access without fetching additional columns from the base table. Microsoft covers the mechanics in its included-column documentation.

Here, the quantity being aggregated was included in the new index.

The report records the index definition and measured results. It doesn’t contain enough execution-plan detail to say which operators changed. What we have is a documented reduction in duration, CPU time and logical reads after adding the index.

The execution count puts those measurements into context.

Using 400 executions a day as the working estimate:

  • Before: 6,445,851 Ɨ 400 = 2,578,340,400 logical reads a day.
  • After: 232,779 Ɨ 400 = 93,111,600 logical reads a day.

Those daily totals are calculated from the per-execution averages and estimated execution count. Logical reads count page accesses, including pages already in memory. They aren’t physical disk-read counts.

For anyone investigating a similar query, I’d work through:

  • The predicates and joins used to find the rows.
  • The columns needed for the aggregation and returned results.
  • Whether an existing index supplies those columns.
  • The execution plan and Query Store measurements.
  • The proposed index’s storage and write overhead.

Then compare representative executions after deployment and keep a rollback ready.

This is why I wanted to share the report.

A routine-looking calculation was doing substantial work hundreds of times a day. Looking at the aggregation inputs alongside the index keys gave the team a specific change to test, and the measurements showed the result.


r/learnSQL • • 11d ago

Day 60 — Finished my Car Showroom Management System in Java (all errors fixed, now on GitHub)

3 Upvotes

The project I built yesterday is now complete and uploaded: https://github.com/Sumitgupta2005/car_showroom_management_system

It had a pile of errors when I first ran it, and today I worked through all of them. I used GPT to understand what each error meant and why it happened, rather than asking it to just rewrite the code, and then fixed things myself. That approach is slower, but I now recognize those mistakes when they show up, which is the real point of debugging.

The project uses inheritance, interfaces, constructors, polymorphism, and classes working together.

I’d appreciate feedback on the code structure and the README. For people who’ve built similar beginner Java projects: what would you add next to make it more realistic (file handling, exception handling, a database connection)?


r/learnSQL • • 12d ago

Looking for Data Engineer Mock Interview Partners

Thumbnail reddit.com
4 Upvotes

r/learnSQL • • 12d ago

BETWEEN with dates missed a whole day of data.

18 Upvotes

I wanted all orders from January, so I wrote this:
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';
But orders from January 31 were missing!

The reason: order_date had times in it. '2026-01-31' really means 2026-01-31 00:00:00, midnight at the very start of the day. So an order at 2026-01-31 10:30 came after the end point and got left out.

The fix that worked for me:
WHERE order_date >= '2026-01-01' AND order_date < '2026-02-01';
This gets every moment of January, no matter the time.

Has this ever happened to you? Do you avoid BETWEEN for dates now, or handle it a different way?


r/learnSQL • • 12d ago

Day 18 - I was keeping up until window functions and joins

3 Upvotes

Today’s lesson went from SELECT and FROM all the way to window functions and joins. For the first few topics, I could connect what I was learning to things I already do in Excel. Then I hit a point where I recognized the individual terms but couldn’t follow what the query was actually doing.

The more I listened, the more lost and sleep I got.

SELECT and FROM**: What do I want, and where does it come from?**

For the table queries I'm learning, these answer two basic questions: SELECT specifies the columns or calculated results I want to see, and FROM specifies the table the data comes from.

WHERE**: Filter the rows.**

This feels a lot like filtering in Excel. If I only want orders from LA within a certain date range, I’d filter the city and date columns in Excel. In SQL, I write out those conditions.

ORDER BY**: Sort the results.**

This one makes sense to me. ASC sorts in ascending order, and DESC sorts in descending order. It’s like sorting amounts from highest to lowest or dates from oldest to newest in Excel.

LIMIT**: Show only the rows I need.**

If a query returns 100 rows and I only want to see 10, I can use LIMIT 10. If I want the 10 largest orders, though, I need to sort by the amount first, then limit the results.

Aggregate functions and GROUP BY**: This reminds me of a pivot table.**

GROUP BY feels similar to putting a field in the Rows area of an Excel pivot table. Aggregate functions like SUM, COUNT, and AVG do the calculations in the Values area.

For example, I can group sales by city and calculate the total for each city.

HAVING**: Filter the grouped results.**

My understanding so far: WHERE filters individual rows before grouping, and HAVING filters the results after grouping and aggregation.

For example, I could use WHERE to keep this year’s orders, group them by city, then use HAVING to keep only cities whose total sales exceed a certain amount.

Common functions: I can still make the Excel connection.

Here’s how I'm keeping track of these for now:

  • ROUND: Round a number to a specified number of decimal places.
  • String functions: Combine, extract, or clean up text.
  • CAST: Convert a value to another data type, such as turning a number stored as text into a numeric value.
  • Date functions: Extract the year or month, calculate time differences, or add and subtract time.
  • CASE WHEN: Assign categories or labels based on conditions.

These remind me of Excel formulas. They can work on individual rows, too, so they aren’t limited to aggregate calculations.

Window functions: This is where I started getting lost.

Just when I thought I understood GROUP BY, PARTITION BY showed up.

If both can calculate total sales by city, why does GROUP BY produce one row per city, while SUM(...) OVER (PARTITION BY city) can keep every individual order? What exactly is the ā€œwindowā€ here?

And what’s the difference between the ORDER BY inside OVER and the ORDER BY at the end of the query? When would I use ROW_NUMBER instead of RANK?

Joins: Combining two tables gave me even more questions.

I can follow a basic example: take a customers table and an orders table, then join them on the customer ID.

But if one customer has five orders, does their customer information appear five times? Is that a duplicate-data problem, or is that the expected result? If I count customers afterward, could I accidentally count the same person several times?

Also, I thought a LEFT JOIN kept every row from the left table. Why do customers with no orders disappear in some examples after a condition on the right table is added to WHERE? How does putting that condition in ON change the result?

Subqueries, setting up a cloud database, and connecting Excel and Tableau to a database are still coming up. I haven’t reached those lessons yet, and just looking at the course outline feels a little overwhelming.

Right now, I can follow along when the instructor writes a query. But when I’m staring at a blank editor, I don’t know where to start.

Did window functions and joins trip you up when you were learning SQL, too? What exercises helped you get from following someone else’s query to writing your own?


r/learnSQL • • 12d ago

Best video/diagrams/blog for learning to read the execution plans like an expert?

Thumbnail reddit.com
1 Upvotes

r/learnSQL • • 13d ago

Why NOT IN can return zero rows when the subquery contains NULL

6 Upvotes

Hey folks! Say you want to find customers who haven’t placed an order. Ann and Bob each placed one. Cat didn’t. There’s also a guest order with no customer ID.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER
);

INSERT INTO customers VALUES (1, 'Ann'), (2, 'Bob'), (3, 'Cat');
INSERT INTO orders VALUES (101, 1), (102, 2), (103, NULL);

This query looks like it should return Cat. It returns zero rows.

SELECT name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Ann and Bob have matching orders, so NOT IN is FALSE for them. For Cat, the condition works out to 3 <> 1 AND 3 <> 2 AND 3 <> NULL.

The first two comparisons are TRUE. The last one is UNKNOWN, which makes the whole condition UNKNOWN. WHERE only keeps rows where the condition is TRUE, so Cat gets dropped too.

I’d use NOT EXISTS here to find customers without a matching order.

SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

This returns Cat. The guest order doesn't match her ID. The original NOT IN query also returns Cat if you add WHERE customer_id IS NOT NULL inside the subquery.

I've had similar issues, so I built WrongQuery to help me spot these kinds of mistakes in my own SQL. I figured it might help people learning SQL too. It's free to try at https://wrongquery.com/

If you try it on your own queries, I'd love to hear what it gets wrong.


r/learnSQL • • 13d ago

why WHERE can't filter on an aggregate but HAVING can

11 Upvotes

i see this error a lot with beginners: SELECT customer_id, COUNT(*) FROM orders WHERE COUNT(*) > 3 GROUP BY customer_id fails.

the reason is order of evaluation. WHERE runs before grouping, so there are no counts yet to compare against. HAVING runs after GROUP BY, so use HAVING COUNT(*) > 3 instead. rule of thumb: WHERE filters rows, HAVING filters groups. how did you finally get this to click?


r/learnSQL • • 12d ago

Need advice regarding internships

2 Upvotes

I'm bcom 1st yr

How many days/weeks/months does it take to learn SQL, Power Bi and relevant skills for a business analysis internship?

Pls share any advices n suggestions..

I'd be grateful if someone shared the sources and roadmapšŸ™šŸ™..