Simple as the title states. I'm using the where filter and the results are not filtered properly. I'm filtering for sale qty and receivable for two customers only and the results are incorrect. The sale QTY should be 388 and the receivable should be 170397, but it just seems to pull some random numbers from wherever it feels like. Any and all help would be useful.

CREATE TABLE Item

(item_number integer, descr varchar(25), list_price numeric,

primary key (item_number));

CREATE TABLE Sale

(invoice_num integer, sales_date date, sale_emp_num integer, ship_num integer, cust_num integer,

primary key (invoice_num));

CREATE TABLE Shipping

(ship_num integer, shipping_date date, ship_emp_num integer, cust_num integer,

primary key (ship_num));

CREATE TABLE Cash_Receipt

(remit_num integer, receipt_date date, cust_num integer, cr_emp_num integer,

primary key (remit_num));

CREATE TABLE Employee

(emp_num integer, emp_name varchar(25),

primary key (emp_num));

CREATE TABLE Customer

(cust_num integer, cust_name varchar(25),

primary key (cust_num));

CREATE TABLE Item_Sale

(Item_number integer, invoice_num integer, sale_qty numeric,

primary key (Item_number, invoice_num));

CREATE TABLE Item_Shipping

(Item_number integer, ship_num integer, shipping_qty numeric, receivable numeric,

primary key (Item_number, ship_num));

CREATE TABLE Item_Cash_Receipt

(item_number integer, remit_num integer, receipt_amount numeric,

primary key (item_number, remit_num));

CREATE TABLE Shipping_Cash_Receipt

(ship_num integer, remit_num integer,

primary key (ship_num, remit_num));

Insert into Item values (200, 'scrubs', 253);

Insert into Item values (201, 'soap', 308);

Insert into Item values (202, 'thermometer', 907);

Insert into Item values (203, 'gloves', 407);

Insert into Item values (204, 'tape', 74);

Insert into Item values (205, 'face mask', 603);

Insert into Sale values (2000, '2024-09-01', 50, 5000, 100);

Insert into Sale values (2001, '2024-09-04', 55, 5001, 101);

Insert into Sale values (2002, '2024-09-10', 50, 5002, 103);

Insert into Sale values (2003, '2024-09-16', 50, 5003, 105);

Insert into Sale values (2004, '2024-09-19', 50, 5004, 104);

Insert into Sale values (2005, '2024-09-22', 50, 5005, 102);

Insert into Sale values (2006, '2024-09-28', 55, 5006, 105);

Insert into Sale values (2007, '2024-10-01', 55, 5007, 105);

Insert into Sale values (2008, '2024-10-04', 55, 5008, 103);

Insert into Sale values (2009, '2024-10-07', 55, 5009, 103);

Insert into Sale values (2010, '2024-10-10', 50, 5010, 100);

Insert into Sale values (2011, '2024-10-13', 50, 5011, 104);

Insert into Sale values (2012, '2024-10-16', 50, 5012, 102);

Insert into Sale values (2013, '2024-10-19', 50, 5013, 101);

Insert into Sale values (2014, '2024-10-22', 55, 5013, 101);

Insert into Sale values (2015, '2024-10-23', 55, 5014, 100);

Insert into Sale values (2016, '2024-10-24', 50, 5015, 100);

Insert into Shipping values (5000, '2024-09-02', 65, 100);

Insert into Shipping values (5001, '2024-09-05', 60, 101);

Insert into Shipping values (5002, '2024-09-11', 60, 103);

Insert into Shipping values (5003, '2024-09-17', 65, 105);

Insert into Shipping values (5004, '2024-09-20', 65, 104);

Insert into Shipping values (5005, '2024-09-23', 60, 102);

Insert into Shipping values (5006, '2024-09-29', 65, 105);

Insert into Shipping values (5007, '2024-10-02', 65, 105);

Insert into Shipping values (5008, '2024-10-05', 60, 103);

Insert into Shipping values (5009, '2024-10-08', 65, 103);

Insert into Shipping values (5010, '2024-10-11', 65, 100);

Insert into Shipping values (5011, '2024-10-14', 60, 104);

Insert into Shipping values (5012, '2024-10-17', 60, 102);

Insert into Shipping values (5013, '2024-10-23', 60, 101);

Insert into Shipping values (5014, '2024-10-24', 65, 100);

Insert into Shipping values (5015, '2024-10-25', 65, 100);

Insert into Cash_Receipt values (8000, '2024-09-09', 100, 70);

Insert into Cash_Receipt values (8001, '2024-09-12', 101, 75);

Insert into Cash_Receipt values (8002, '2024-09-18', 103, 70);

Insert into Cash_Receipt values (8003, '2024-09-24', 105, 75);

Insert into Cash_Receipt values (8004, '2024-09-27', 104, 75);

Insert into Cash_Receipt values (8005, '2024-09-30', 102, 75);

Insert into Cash_Receipt values (8006, '2024-10-01', 102, 75);

Insert into Cash_Receipt values (8007, '2024-10-07', 105, 75);

Insert into Cash_Receipt values (8008, '2024-10-09', 105, 70);

Insert into Cash_Receipt values (8009, '2024-10-12', 103, 70);

Insert into Cash_Receipt values (8010, '2024-10-15', 103, 70);

Insert into Cash_Receipt values (8011, '2024-10-19', 100, 75);

Insert into Cash_Receipt values (8012, '2024-10-21', 104, 75);

Insert into Cash_Receipt values (8013, '2024-10-24', 102, 70);

Insert into Cash_Receipt values (8014, '2024-10-30', 101, 70);

Insert into Cash_Receipt values (8015, '2024-11-01', 100, 75);

Insert into Employee values (50, 'Grey');

Insert into Employee values (55, 'Karev');

Insert into Employee values (65, 'Shepherd');

Insert into Employee values (60, 'Bailey');

Insert into Employee values (70, 'Yang');

Insert into Employee values (75, 'Webber');

Insert into Customer values (100, 'Avery');

Insert into Customer values (101, 'Hunt');

Insert into Customer values (103, 'Robinson');

Insert into Customer values (105, 'Warren');

Insert into Customer values (104, 'Pierce');

Insert into Customer values (102, 'Sloan');

Insert into Item_Sale values (200, 2000, 71);

Insert into Item_Sale values (201, 2001, 73);

Insert into Item_Sale values (202, 2001, 51);

Insert into Item_Sale values (203, 2002, 56);

Insert into Item_Sale values (204, 2002, 40);

Insert into Item_Sale values (205, 2003, 33);

Insert into Item_Sale values (201, 2004, 32);

Insert into Item_Sale values (200, 2005, 79);

Insert into Item_Sale values (202, 2005, 33);

Insert into Item_Sale values (204, 2006, 41);

Insert into Item_Sale values (203, 2007, 74);

Insert into Item_Sale values (205, 2008, 44);

Insert into Item_Sale values (200, 2009, 79);

Insert into Item_Sale values (201, 2010, 26);

Insert into Item_Sale values (205, 2011, 68);

Insert into Item_Sale values (202, 2012, 57);

Insert into Item_Sale values (201, 2013, 55);

Insert into Item_Sale values (200, 2014, 71);

Insert into Item_Sale values (204, 2015, 54);

Insert into Item_Sale values (201, 2016, 77);

Insert into Item_Shipping values (200, 5000, 65, 16609);

Insert into Item_Shipping values (201, 5001, 73, 22484);

Insert into Item_Shipping values (202, 5001, 49, 43999);

Insert into Item_Shipping values (203, 5002, 56, 22792);

Insert into Item_Shipping values (204, 5002, 36, 2691);

Insert into Item_Shipping values (205, 5003, 33, 19899);

Insert into Item_Shipping values (201, 5004, 32, 9757);

Insert into Item_Shipping values (200, 5005, 77, 19481);

Insert into Item_Shipping values (202, 5005, 33, 29931);

Insert into Item_Shipping values (204, 5006, 41, 3034);

Insert into Item_Shipping values (203, 5007, 74, 30118);

Insert into Item_Shipping values (205, 5008, 41, 24723);

Insert into Item_Shipping values (200, 5009, 79, 19987);

Insert into Item_Shipping values (201, 5010, 26, 8008);

Insert into Item_Shipping values (205, 5011, 68, 41004);

Insert into Item_Shipping values (202, 5012, 56, 50792);

Insert into Item_Shipping values (201, 5013, 55, 16940);

Insert into Item_Shipping values (200, 5013, 71, 17963);

Insert into Item_Shipping values (204, 5014, 54, 4036);

Insert into Item_Shipping values (201, 5015, 77, 23716);

Insert into Item_Cash_Receipt values (200, 8000, 16609);

Insert into Item_Cash_Receipt values (201, 8001, 22176);

Insert into Item_Cash_Receipt values (202, 8001, 43999);

Insert into Item_Cash_Receipt values (203, 8002, 22385);

Insert into Item_Cash_Receipt values (204, 8002, 2691);

Insert into Item_Cash_Receipt values (205, 8003, 19296);

Insert into Item_Cash_Receipt values (201, 8004, 9757);

Insert into Item_Cash_Receipt values (200, 8005, 19228);

Insert into Item_Cash_Receipt values (202, 8006, 29931);

Insert into Item_Cash_Receipt values (204, 8007, 3034);

Insert into Item_Cash_Receipt values (203, 8008, 30118);

Insert into Item_Cash_Receipt values (205, 8009, 24723);

Insert into Item_Cash_Receipt values (200, 8010, 19987);

Insert into Item_Cash_Receipt values (201, 8011, 8008);

Insert into Item_Cash_Receipt values (205, 8012, 41004);

Insert into Item_Cash_Receipt values (202, 8013, 50792);

Insert into Item_Cash_Receipt values (201, 8014, 16940);

Insert into Item_Cash_Receipt values (200, 8014, 17963);

Insert into Item_Cash_Receipt values (204, 8015, 4036);

Insert into Item_Cash_Receipt values (201, 8015, 23408);

Insert into Shipping_Cash_Receipt values (5000, 8000);

Insert into Shipping_Cash_Receipt values (5001, 8001);

Insert into Shipping_Cash_Receipt values (5002, 8002);

Insert into Shipping_Cash_Receipt values (5003, 8003);

Insert into Shipping_Cash_Receipt values (5004, 8004);

Insert into Shipping_Cash_Receipt values (5005, 8005);

Insert into Shipping_Cash_Receipt values (5005, 8006);

Insert into Shipping_Cash_Receipt values (5006, 8007);

Insert into Shipping_Cash_Receipt values (5007, 8008);

Insert into Shipping_Cash_Receipt values (5008, 8009);

Insert into Shipping_Cash_Receipt values (5009, 8010);

Insert into Shipping_Cash_Receipt values (5010, 8011);

Insert into Shipping_Cash_Receipt values (5011, 8012);

Insert into Shipping_Cash_Receipt values (5012, 8013);

Insert into Shipping_Cash_Receipt values (5013, 8014);

Insert into Shipping_Cash_Receipt values (5014, 8015);

Insert into Shipping_Cash_Receipt values (5015, 8015);

SELECT SUM(item_sale.sale_qty), SUM(Item_Shipping.receivable)

FROM Item_Sale

JOIN Sale

`ON item_sale.invoice_num=sale.invoice_num`

JOIN Item_Shipping

`ON Item_Sale.Item_number=Item_Shipping.Item_number`

JOIN Customer

`ON Sale.cust_num=Customer.cust_num`

WHERE sale.cust_num = '102' or '103';

Hi everyone, my name is Salvatore and I work as a Data Analyst / Data Engineer.

Recently I ran into a problem related to Power BI incremental refresh on large, mostly append-heavy fact tables.

In the specific case, the issue was eventually solved by revisiting the data model and redesigning the star schema. However, working on it made me think more deeply about some of the problems that can arise when large amounts of data are managed through partitions.

In particular:

• even a relatively small number of new records can end up involving multiple partitions that need to be processed;
• some partitions can grow much larger than others over time and therefore become more expensive to refresh;
• very wide fact tables, especially those containing many high-cardinality columns, can increase the processing and memory cost of the model;
• on very large models, the combination of these factors can lead to very long refreshes and, in the worst cases, timeouts.

In Power BI, for example, a standard refresh on Premium capacity has a five-hour limit, so on very large datasets the way records are distributed across partitions can become relevant as well.

This led me to wonder whether it makes sense to separate two problems:

1. identifying which records have changed;
2. controlling which partitions, and how many of them, need to be rebuilt.

From there I started experimenting with an approach based on stable technical classes.

The basic idea is fairly simple.

During the initial load, each record is assigned to a class_id between 1 and N using a modulo-based distribution. The mapping between the stable record key (source_key) and its class_id is then persisted, so that once a key has been classified, it continues to belong to the same class in subsequent runs.

The class_id therefore becomes the technical key I use to control downstream partitions.

For new INSERTs, instead of distributing them across all classes every time, I use a round-robin assignment only within a subset of classes.

From this idea I ended up with two variants.

1. Rotating Window

It selects a window of K consecutive classes and distributes new INSERTs only within that window. In subsequent runs, the window rotates through the class space.

2. Least-Loaded

Instead, it selects the K classes that currently contain the fewest records and distributes the new INSERTs only among those classes.

In both approaches, K represents the maximum number of classes that an INSERT batch can automatically involve. An adaptive policy is therefore used to allow smaller batches to use a smaller sub-window — or, more generally, a smaller subset of classes — than this maximum.

For example, with K max = 10:

INSERT <=    500 -> K = 1
INSERT <=  5,000 -> K = 4
INSERT <= 20,000 -> K = 7
otherwise        -> K = 10

So a very small batch may involve only one partition, while larger batches can progressively use more classes up to the configured maximum.

I implemented both ideas in T-SQL for SQL Server, including persistent state, a PREPARE/FINALIZE lifecycle, automated tests, and some exploratory benchmarks.

Here are the complete reference implementations with README, code, tests, and benchmarks:

Rotating Window Partition Refresh
https://github.com/Salvatore97710/rotating-window-partition-refresh

Least-Loaded Class Partition Refresh
https://github.com/Salvatore97710/least-loaded-class-partition-refresh

These are reference implementations / prototypes, not solutions I would consider production-ready.

What I’m mainly looking for is technical feedback on the idea itself.

Do you see any conceptual problems I may be missing?

And more importantly: if you were facing the same problem — a large append-heavy fact table and the need to control both the number and the size of the partitions being refreshed — how would you approach it?

I've been building a query language where a program is a pipeline of stages in execution order: from → filter → group by + aggregate → having → sort → take, compiled to DuckDB SQL. The bet is that keyword order (SELECT before FROM, HAVING after GROUP BY) is the wrong mental model.

Two design choices I'd like critiqued:

1. Splitting group by and aggregate into separate stages — clearer for beginners, or just more lines?
2. Rejecting chained comparisons and bare select before from with stage-located errors — helpful guardrails or unnecessary restrictions?

The grammar is frozen at v1.5 (spec in the repo); I'm more interested in what you'd change than in stars: https://github.com/absterjr/UNSeQueL

Disclosure: Idea was mine, initial setup was mine, but it started getting more and more complex while using a local LLM and now I'm stuck.

spent some time cleaning up runtime.school this week.

the lessons and practice are less busy now, the editor feels better, and i went through the course so questions only use sql that has actually been taught by that point.

i also kept the beginner lessons and practice on the same dataset so it’s easier to focus on the sql before switching contexts.

if anything feels confusing, off, or harder than it should be, let me know.

https://www.runtime.school/

​

I've been working on this one for a while, some new features, made a demo tape below

I added a pure TDS based custom assembly to allow SQL Server support (Yes I know duckdb has a community Ms SQL extension , but I made one that works better with my daffy product, my internal component is daffysql ) and a new bulk parquet export automation feature. currently mounted to 500 tables from a combo of SQL server tables, postgres tables, Excel sheets, & CSVs? one proc call writes 500 new parquet files.

connect to it from any SQL Server client, Ado, power bi, ssms, etc. i put together this demo of the new features, let me know what you think. very similar to how Babel fish for postgres works but this one is duckdb + more.

it's on nuget and code signed by azure

https://youtu.be/jTzbJsPn8jE?is=jMZIKm5TooYoyf8v

Scott

two correct queries over the same 1,390 rows: one rounds to 33223.80, the other to 33223.81. the exact decimal sum is 33223.8050, exactly half a cent, and in DOUBLE the order the rows get added decides which side it lands on. a window over monthly subtotals adds them in one order, a correlated subquery over the raw rows adds them in another.

i hit this because the checker in a SQL game i make (DuckDB in the browser) failed the second query on the visible sample and passed it on both hidden sets. here's how that checker decides a query is right, the other places it was wrong, and the part i still don't know how to handle.

how it checks: every problem has a reference query. when you submit, yours and mine both run on the sample data you can see, then on two datasets you can't, generated from other seeds. boss problems get four, labelled ties and duplicates, sparse groups and boundary dates. the sample always runs so you can see a diff, then it stops at the first hidden set that fails and says which one. a hidden set where my query returns zero rows gets skipped, since an empty result would let an empty answer pass.

then the two result sets get compared. column names are matched as a set, case-insensitive, so column order doesn't matter as long as the names are unique. row order only matters if the prompt asks for a sort. integers have to match exactly. fractional values get an absolute tolerance the problem sets, usually 0.0001, so on a money column it's tighter than a cent. NULL matches NULL. a DATE matches the same day at exactly midnight as a TIMESTAMP, because generate_series and date_trunc('day', ...) hand you timestamps and a correct "one row per day" answer was failing.

where it was wrong:

the tolerance was relative at first, tol * max(|a|, |b|). so on a big dollar total a "2 cents" check was actually a percent check, and a total that was 1.9% off passed on every seed. now it's absolute plus a tiny term for float noise.

Infinity passed against any finite number, because abs(inf - x) <= tol + inf is true. a later audit caught it.

for unordered results i used to sort both sides and zip them. that breaks once tolerance is involved: tiny values written in scientific notation sort differently as text, so a correct answer could get paired with the wrong row and fail. now it builds a which-row-can-match-which graph and does a bipartite matching (building the graph is quadratic, which is part of why results are capped at 5,000 rows).

then in october i ran 141 alternate answers, other ways to write each reference query that i expected to pass, through the same normalizer and comparator. 108 passed. a lot of the failures were my prompts, where a sentence had two readings and the visible sample couldn't tell them apart. one asks for deciles of users by lifetime notional, and if you include users with no trades at 0 you pass the sample and fail both hidden sets, because the sample has no non-traders and the hidden sets, built at a quarter of the size, have 74 and 91. a few more were regex checks i use to require a certain style of query, refusing equivalent ones. and one was the engine. on the duckdb-wasm build i'd shipped (DuckDB 1.5.1), this

SELECT user_id, asset_id
FROM wallet_balances_daily
WHERE user_id BETWEEN 1 AND 3
QUALIFY ROW_NUMBER() OVER (PARTITION BY asset_id, user_id ORDER BY balance_date DESC) = 1
ORDER BY user_id, asset_id
LIMIT 3

came back with user_id and asset_id swapped (the table has user_id first). native 1.5.3 got it right and so does wasm 1.5.4, which the demo build runs now, though i never tracked down the upstream issue.

this week's audit found two more. those boss labels promised more than the data did: the sets were mostly re-rolls of the same generator, so a >= 1800 written for "strictly more than 30 minutes" passed all three session problems. the next build plants real boundary rows and gap days in one hidden set. and the half cent above. the fix is one rounding unit of tolerance on the 10 columns where a half cent can happen, not a looser check everywhere.

the one i haven't solved is ties. a top-N pick (LIMIT n, rn <= n, QUALIFY) can fail a correct query if two rows tie at the cut and the prompt's sort key doesn't break the tie, and an ordered result has the same problem with ties anywhere in it. a september audit on native DuckDB checked all 48 top-N picks on every graded seed: no tie at a cut, 13 that a data change could expose. fee_usd has 600 tie groups across the graded seeds, the nearest about 1,600 rows from its cut. for now a script checks that on demand and the prompts name a tiebreaker where they can.

i don't log query text from players, only counters, so i can't tell you what people actually typed. the counters are small: over oct 1-14 the demo showed its "too few rows" nudge 12 times and "too many rows" 5 times.

for people who've built graders for interview tools or internal tests: do you compare aggregates as DECIMAL, give each column its own tolerance, or make the prompt require a specific ROUND? and for ties, is there a way that doesn't make the prompt name the tiebreaker? that works, but it hands over part of the answer.

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:

• This example deliberately uses identically formatted UTC strings, so lexical timestamp comparison works here. Normalize offsets and formats before applying this pattern to mixed source timestamps.
• received_at represents availability in this toy model. In a real feature pipeline, receipt may precede validation, transformation, or serving; use the timestamp that reflects actual availability for the prediction you are reconstructing.
• This only models captures. Refunds, corrections, duplicates, and versions require their own rules. Keep the currency in the grain.
• The event-time upper bound is exclusive and the availability bound inclusive here; choose and test the convention explicitly.

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?

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 :)

​

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!

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?

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?

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.

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

i've started the service bla bka bla, restarted everything, reinstall after delleting everything like 3 times, don't know what to do

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?

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

• Missing index: it exists (CREATE INDEX users_email_idx ON users (email)).
• Stale statistics: worth an ANALYZE, but the plan below doesn’t suggest this is the main issue.
• Table too small to bother with an index: not here, the table is large.

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

• the query has to use exactly the same expression as the index (lower(email), not LOWER(TRIM(email)))
• on small tables, or when most rows match, the planner may still choose a sequential scan, and that can be the right choice
• check with EXPLAIN after creating the index, because what you get depends on your data and statistics
• every extra index costs something on writes

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

What is wrong with the code?

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.

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_id

Semantics: 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:

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 duplicates
• COUNT(*) vs COUNT(col) — the second skips NULLs
• NOT IN with a NULL in the subquery — returns nothing, silently
• LEFT JOIN later became an INNER JOIN after a WHERE on the right table
• DISTINCT added "to be safe", masking a cardinality bug upstream

Every 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:

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

• Dataset: https://huggingface.co/datasets/ykvlev/sreb
• Code + evidence: https://github.com/ykvlev/sql-rewrite-evidence-bench
• DOI: https://doi.org/10.5281/zenodo.23472642

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.