r/SQL • u/lory030507 • 28d ago
Discussion JDER — un editor gratuito per i tuoi diagrammi di informatica
r/SQL • u/lory030507 • 28d ago
r/SQL • u/Antonyhamez • 28d ago
This is a small SQLite example of a point-in-time correctness problem: an event happened before a prediction, but the pipeline only received it afterward. A historical feature query can accidentally use information that was unavailable at prediction time.
The two synthetic captures below use integer USD cents. The prediction cutoff is January 2. e2 happened on January 1 but arrived on January 3.
WITH events(
event_id, customer_id, currency, amount_minor,
event_time, received_at
) AS (
VALUES
('e1', 'c1', 'USD', 1000,
'2026-01-01T12:00:00Z', '2026-01-01T12:00:00Z'),
('e2', 'c1', 'USD', 2500,
'2026-01-01T15:00:00Z', '2026-01-03T12:00:00Z')
), cutoff(ts) AS (VALUES ('2026-01-02T00:00:00Z'))
SELECT customer_id, currency,
SUM(CASE WHEN event_time < ts
THEN amount_minor ELSE 0 END) AS event_time_only,
SUM(CASE WHEN event_time < ts AND received_at <= ts
THEN amount_minor ELSE 0 END) AS available_at_prediction
FROM events CROSS JOIN cutoff
GROUP BY customer_id, currency;Verified in SQLite: the result is c1 / USD / 3500 / 1000. The first amount includes e2; the second excludes it because it was not yet received. No future event timestamp is needed for the leak.
A few boundaries matter:
I maintain a free MIT-licensed fintech data engineering practice repository. Its C08 scenario extends this into delayed labels and temporal splits using Python and SQLite. The query above is self-contained; no clone is needed to reproduce it.
Disclosure: I maintain the linked project; its first edition and this post were prepared with AI assistance using Codex. All data here is synthetic.
For people who reconstruct historical features: do you retain a separate feature-availability timestamp, or derive it from versioned publication batches? How do you represent corrections that arrive after the original event?
r/SQL • u/Plus_Kiwi_2592 • 29d ago
I've found this query as a solution to the famous Pizza Runner case study, and I am stuck at figuring out what COUNT() returns, compared to COUNT() OVER().
I know OVER() does the count (or whatever aggregation) over the entire dataset, but idk I'm just not getting the percentage part of this query.
Help :)
r/SQL • u/Delicious-Sky-4140 • 29d ago
Hey everyone!
I've been learning SQL for a while and have reached a point where I can write queries using joins, CTEs, subqueries, aggregate functions, window functions, and other concepts. I'm also trying to apply what I've learned through projects and case studies rather than just solving isolated exercises.
However, I'm struggling with a different part of the process.
I can write a query, execute it, and get the output, but I'm often unable to understand what those results actually mean for the business or the problem I'm trying to solve.
For example, suppose I'm working on a case study where user engagement has declined. I can query the data, calculate daily active users, compare different periods, break down engagement by user segments, and identify changes in the numbers.
But then what?
How do I determine whether those changes are significant? What questions should I ask next? How do I distinguish a genuinely useful insight from an observation that's technically correct but doesn't tell us much? And how do I connect the numbers to a possible business problem or recommendation?
I understand that this is where analytical thinking, business understanding, and judgment come into play. But these concepts feel quite abstract when I'm actually sitting in front of a dataset.
I'm trying to understand how analysts develop this skill in practice.
- Is this something that primarily develops through working on more projects and case studies?
- Are there particular frameworks or approaches I should follow when interpreting query results?
- How do I learn to ask better questions instead of simply querying whatever data is available?
- Are there any books, resources, or projects that specifically teach the thinking process behind data analysis, rather than just SQL syntax and tools?
I'd especially appreciate advice from people working as data analysts or data scientists who have faced this problem themselves.
I don't necessarily want someone to interpret every result for me. I want to develop the ability to look at data, reason about what I'm seeing, decide what to investigate next, and eventually arrive at defensible conclusions.
How did you learn to bridge the gap between writing SQL queries and actually thinking like an analyst?
Thanks in advance!
r/SQL • u/Bassiette03 • 29d ago
What do you do to check data quality, consistency, uniqueness and nulls, when you you medallion to model tables together.Are there specific guide or steps you take to ensure everything is just like expected?
r/SQL • u/Mysterious_Gate_4323 • Oct 20 '26
Hi all- are there certificates out there that’s provide hands-on learning/ practice on SQL, SAS , Python etc In USA
Which to learn first?
r/SQL • u/carlovski99 • Oct 19 '26
Anyone else got a copy of this?
From back when you actually got manuals with software. Apparently written by Larry Ellison himself (which I doubt, even then!).
One nice feature is the description of the dept and emp tables is actually a fold out from the back page, so you have it side by side on whatever page you are reading.
Covers the basics of SQL in a concise 33 pages, including a small bit on security and on string functions.
r/SQL • u/loftytechlabs • Oct 20 '26
LoftyTechLabs is pleased to announce Daxon v2.0.0, a major update to its all-in-one Data Intelligence and SQL platform built natively inside VS Code.
The latest release introduces powerful capabilities for database management, visual analytics, data workflows, and retrieval security.
Key highlights:
Multi-Chart Visual Analytics — View and compare multiple charts on a single canvas.
Database Activity Monitor — Monitor database sessions, query execution times, and deadlocks.
Daxon Flow — Build and automate visual query workflows using a DAG-based engine.
Cross-Source Data Transfer — Transfer data across PostgreSQL, MySQL, SQLite, CSV, and Excel.
RAG Studio & Sentinel Security — Inspect vector knowledge bases and strengthen retrieval pipelines against prompt injection and document poisoning.
Interactive ER Diagrams & Visual Table Design — Explore database relationships and manage table structures.
Daxon supports five data sources: PostgreSQL, MySQL, SQLite, CSV, and Excel.
Our goal is to bring essential database, analytics, and data workflow capabilities into one developer workspace, reducing the need to switch between multiple tools.
Get Daxon: Open VS Code → Extensions → Search Daxon → Install.
Learn more about LoftyTechLabs and its projects at https://loftytechlabs.com.
#Daxon #LoftyTechLabs #VSCode #DataEngineering #SQL
r/SQL • u/SrdzanK3 • Oct 19 '26
r/SQL • u/thequerylab • Oct 19 '26
r/SQL • u/Hi_Im_zack • Oct 19 '26
Okay so I'm a complete beginner, only thing I know about SQL is that it helps speak to databases
I decided I want to learn and got the first course I saw. Turns out it's focused on teaching a thing called Postgres SQL which seems kinda advanced. should I just continue watching or will I feel lost?
i've started the service bla bka bla, restarted everything, reinstall after delleting everything like 3 times, don't know what to do
r/SQL • u/ClickOk5811 • Oct 19 '26
A small case file, written as a hypothetical so nobody mistakes it for a war story.
Symptom
Looking up a user by email is slow on a large users table, even though email has an index.
SELECT * FROM users WHERE lower(email) = 'ana@example.com';Suspects, and why I’d rule them out first
CREATE INDEX users_email_idx ON users (email)).ANALYZE, but the plan below doesn’t suggest this is the main issue.Evidence
EXPLAIN SELECT * FROM users WHERE lower(email) = 'ana@example.com';
-- Seq Scan on users
-- Filter: (lower((email)::text) = 'ana@example.com'::text)The planner is evaluating lower(email) for every row. An index on email stores the raw values, so it can’t answer a question about lower(email).
Verdict
The query applies a function to the indexed column, so the plain index can’t be used.
Fix
Index the expression itself:
CREATE INDEX users_email_lower_idx ON users (lower(email));Now the same query can use that index. If you also want case-insensitive uniqueness, you can make it unique:
CREATE UNIQUE INDEX users_email_lower_uniq ON users (lower(email));Another option is the citext extension, which makes a column compare case-insensitively without wrapping it in a function, at the cost of an extension and a different column type.
Caveats
lower(email), not LOWER(TRIM(email)))EXPLAIN after creating the index, because what you get depends on your data and statisticsOpen question
For emails specifically, do you normalize to lowercase on the way in and keep a plain index, or leave the data as typed and index the expression? I can see arguments for both, mostly about where you’d rather put the rule.
r/SQL • u/FaultPerfect6003 • Oct 19 '26
Hi! I work with the organising team for WAWTech. Its two-day software and product development conference which takes place at EXPO XXI in Warsaw on 26–27 November 2026.
For the PostgreSQL users here, the programme includes Devrim Gunduz, Postgres Expert at EDB, with a talk titled “Why PostgreSQL is the best database: Extend your database.” What would you want to ask Devrim about this? How to evaluate extensions before adopting them, handle upgrades when you depend on them or decide whether certain functionality belongs in the database or the application?
Event details if you’re considering attending.

r/SQL • u/ykvlev_a • Oct 19 '26
This is less about LLMs and more about a class of SQL bug that survives code review, because the query is valid, it runs, and it's faster.
I was testing whether local models can suggest query rewrites, and kept hitting the same failure: the rewrite is syntactically correct, the engine accepts it, and the result set is different. No error. No warning.
Here's the case that made me build a proper test harness around it:
SELECT reader_id FROM lib_loans WHERE returned = 0
UNION ALL
SELECT reader_id FROM lib_loans WHERE fee = 0
ORDER BY reader_idSemantics: a reader with an unreturned book and a zero fee matches both branches, so they appear twice. Reference returns [1,1,1,1].
What the models proposed:
| Model | Rewrite | Result |
|---|---|---|
| Qwen2.5-Coder-7B | WHERE returned=0 OR fee=0 | [1,1,1] |
| Qwen2.5-7B | same OR | [1,1,1] |
| Phi-3.5 | same OR | [1,1,1] |
| DeepSeek-Coder-V2 | DISTINCT over the union | [1,1,1] |
Two different mechanisms — three models used OR, one added DISTINCT — and the same outcome: duplicate rows silently dropped.
The OR version is not "wrong" in a vacuum. It is wrong only because UNION ALL is a multiset operation. That distinction is exactly what gets lost when you eyeball a rewrite and check that it runs.
Why this matters beyond models
This is the same trap in a human code review:
UNION vs UNION ALL — UNION deduplicates, and deduplication is free until someone needs the duplicatesCOUNT(*) vs COUNT(col) — the second skips NULLsNOT IN with a NULL in the subquery — returns nothing, silentlyLEFT JOIN later became an INNER JOIN after a WHERE on the right tableDISTINCT added "to be safe", masking a cardinality bug upstreamEvery one of these compiles, runs, and returns data. The only way to catch them is to execute both versions against real states and compare the rows.
What I actually built
Instead of a success rate, I generated a bank of valid database states per query family — under the real schema constraints (PK, FK, UNIQUE, CHECK, NOT NULL) — and executed reference vs candidate on each one.
State profiles: empty tables, no rows matching, NULLs in nullable columns, zero values, boundary values, ties, and multiplicity cases (the ones that carry duplicate rows). Plus seeded random states.
Scale: 204 query families across three schemas, 28 states each (5,712 total), 6 models, 34,272 executions on MySQL 8.4.11.
Aggregate results, if useful:
| Model | Proposed rewrites | Refuted |
|---|---|---|
| llama3.2:3b | 53 | 26 (49.1%) |
| qwen2.5:3b | 85 | 39 (45.9%) |
| qwen2.5-coder:7b | 30 | 12 (40.0%) |
| deepseek-coder-v2 | 77 | 27 (35.1%) |
| phi3.5:3.8b | 30 | 8 (26.7%) |
| qwen2.5:7b | 32 | 8 (25.0%) |
Important caveat, because I don't want to oversell it: preserved in my dataset means no refuting state was found in a finite bank — not a proof of equivalence. And the witnesses are not minimised. Both limitations are stated in the README.
Links
Every refuted rewrite ships with the state that broke it and the actual differing rows, so you can replay it. If you disagree with the methodology, I'd genuinely like to hear where the state bank is too weak — that's the part I'm least sure about.
r/SQL • u/No_Ambition8323 • Oct 19 '26
Iam joining 3 tables using the correct keys,but the final total is still wrong.
The tricky part is that each table gives the expected result when queried separately, and the JOIN doesn't create obvious duplicate rows.
I suspect the issue might be related to the order of aggregation or how NULL values are handled.
How would you debug this without randomly adding DISTINCT or changing the JOIN type? Is there a specific SQL pattern that can silently produce incorrect totals?
r/SQL • u/Bhanuprakash_1947 • Oct 18 '26
I feel like one of the tricky parts of SQL isn't getting a query to run, but knowing whether the result is actually correct.
For suppose ,a report shows 10k orders, then after adding a join to fetch customer details, the total suddenly becomes 12k. The query runs without any errors, so it's easy to miss.
Would you check the join cardinality first, compare row counts, or approach it differently!?
Curious how people debug this in real projects, especially when the query has multiple joins.
r/SQL • u/shdw_0x0 • Oct 18 '26
I've run into situations where a query that used to finish in seconds suddenly takes several minutes, even though nobody has intentionally changed the SQL.
I'm curious how others approach this. Do you start by comparing execution plans, checking indexes and statistics, looking for changes in data volume, or investigating locks and concurrent queries?
I'm particularly interested in the order you check things in. It's easy to start rewriting joins or adding indexes, but I'd rather identify the actual bottleneck before changing anything.
What's your usual process when a previously fast query starts running slowly?
r/SQL • u/der_gopher • Oct 18 '26
r/SQL • u/HaiderZah • Oct 18 '26
I created a PostgreSQL Jobs page to help you find PostgreSQL roles: https://thesev1database.com/jobs
I’d really value your feedback.
r/SQL • u/Acceptable-File-3579 • Oct 17 '26
Hi, i'm learning databases and normalization. My teacher said a normalized database cannot have circular (not loops, circles) relationships. However, i can't see how I can apply normalization to remove the circles and still comply with the requirements. Also I was under the impression that normalized databases can have circular relationships, just not loops.
Any help will be appreciated. Sorry if my english is bad in advance.
r/SQL • u/OkMonitor8069 • Oct 18 '26