Shortly after SQLazy was launched, we offer it to a group of developers to test using their own queries.

Overall, the feedback was positive. Most developers were able to get their workflows running on the first try and put the compiled SQL straight to use. The most frequently praised features were that the generated SQL didn’t invent column names, every step could be inspected, and even old queries that they had once dared not touch could now be reworked. But we also received plenty of negative feedbacks, some of it quite pointed. These pain points weren’t all the same, so we grouped them into two categories.

Some capabilities aren’t clearly explained in the documentation. For example, if you want to retrieve only duplicate rows and exclude unique ones, you can handle this directly using the three options of the deduplication feature. To check whether a value belongs to a set, simply use the contain function. These cases don’t require any code changes; a clearer explanation is all that’s needed.

The capability is there, but the syntax is cumbersome. You can express what you want, but it takes a roundabout approach, or the reader has to scrutinize the code to understand it. What we learned from this category of feedback is that users often get stuck not because the functionality is missing, but because the code is cumbersome to write and read. So, we made some changes in this area. The following are the most worthwhile improvements from this round. Each change may look small, but it addresses a need that comes up repeatedly in everyday use.

Min/Max Aggregation: Getting related attributes, not the min/max value itself

Many developers have run into the same problem: they want a different field from the record containing the maximum or minimum value – for example, the amount of the most recent order.

SQL has no built-in, one-step way to do this, so developers usually resort to a subquery: first find the latest date for each customer, then filter the main table to retrieve the corresponding records, often combining this with aggregation along the way:

SELECT ClientID, MAX(Amount) AS MaxAmt FROM orders o

WHERE OrderDate = (SELECT MAX(OrderDate) FROM orders WHERE ClientID = o.ClientID)

GROUP BY ClientID;

One thing to keep in mind is that multiple records may correspond to the same minimum or maximum value, so a second round of aggregation may be needed. Of course, applying aggregation again won’t hurt even if there’s only one matching record.

A few databases, such as Oracle, let you do this in a single step using a proprietary KEEP syntax (MAX(Amount) KEEP (DENSE_RANK LAST ORDER BY OrderDate)). Other databases don’t support it, so the logic has to be broken into multiple steps.

SQLazy previously required two steps: first, filter for records of the latest date, then find the maximum value among those records.

t2=orders: rank OrderDate; max; partition ClientID

t3=t2: summarize max Amount; group ClientID

Now, we’ve simplified this entire category of queries into a single line of SQLazy code: find the record or records with the minimum or maximum value, then retrieve another field from those records.

result= summarize argmax OrderDate max Amount; group ClientID

argmax takes only a single value aggregation after it. It first pins down the one or more records for the latest OrderDate, then takes the largest amount among them. It’s written in one statement, and it works on any database.

Computed Columns: Cross-row metrics in one Pass, not two

Cross-row metrics such as percentage of total and cumulative sums used to get no dedicated handling. They could be done, but only by using the computed column function twice, which made the code cumbersome.

For example, say the orders table is sorted by salesperson (SellerId), and you want to calculate each order’s share of its salesperson’s total sales. The usual SQL approach is to use a window function (a window per group): calculate the total in one pass, then divide:

SELECT OrderID, Client, SellerId, Amount, OrderDate,

SUM(Amount) OVER (PARTITION BY SellerId) AS SellerTotal,

Amount / SUM(Amount) OVER (PARTITION BY SellerId) AS AmountShare

FROM orders;

Previously, SQLazy code had to calculate twice: first compute the total and store it in a column, then divide:

t1 orders compute summarize, Amount, Name SellerTotal; segment SellerId

t2 t1 compute Amount/ SellerTotal, name AmountShare

Now we’ve added a convenient cross-row parameter, so the SQLazy code takes just one statement. Note that this is something we added, not something that was there originally:

t1 orders compute Amount, Share, Name AmountShare; segment SellerId

In the new code, “share” is simply a cross-row parameter. It calculates each row’s share of its group’s total in one go, with no need for the separate total column. In the same way, cumulative sum, cumulative share, growth rate, same-period comparison, and YoY growth each have a corresponding cross-row parameter, and each takes just one statement.

What we learned is this: cross-row metrics are something everyone has to compute. What gets convoluted is not the machine but the people reading the code. It can be written in two steps, but you first need to understand what that intermediate column is for.

Segmentation, alignment, and ranking

There are endlessly many possible bases for segmentation, and in the past you could only hard-write them with conditional expressions. For example, “start a new segment when the value changes” had to be written as a comparison with the previous row plus special handling for a null first row, and the reader had to walk through it in their head to be sure it was right. Here’s what we took from this: the bases look endless, but only a handful are in common use. So, we distilled the common ones into eight enumerated options: changed, unchanged, increased, not increased, decreased, not decreased, plus a total number of groups and grouped every N rows. You just state the basis.

Reports require continuous dates, so a day with no data still has to keep a row. The feedback made us realize that what makes empty rows fill-in awkward is not the writing itself, but having to specify, across three steps, which column is the alignment basis. The usual SQL approach is to generate a date sequence and left-join it back to the original table. SQLazy used to require the same two steps, generating the sequence and then joining. So, we folded the three steps into one align. Now a single statement aligns rows to a basis and fills in the empty ones. Its parameters are the alignment basis, sort-only, and keep other columns and segments.

Getting the top N is a request you encounter every day, yet it used to take two steps: calculate a rank column (ordering rows by some column), then filter on it. So, we made rank function select target rows directly by rank, with top function and bottom function taking a count, and max and min functions retrieving records.

Why these changes were worth it

None of these changes is large, but they all land on the same kind of thing: “lock a batch of records by a definition, then aggregate,” “calculate the share and cumulative sum within a segment,” “segment by a business basis,” “fill in the missing dates,” “take the top N.” Every one of these is a need that comes up every day. After the changes, code that used to be written in a roundabout way is now a single statement.

The application scope hasn’t changed: for simple select, insert, update and delete, writing SQL directly is easier. Macros are still on the roadmap, and recursion is about to be released. The capabilities above aren’t everything, either. We only changed the places receiving the most feedback.

SQLazy: Complex SQL, made lazy. Trusted by design.

Post the pitfalls you encountered to our forum, whether they were generated by AI or left behind by someone else. Each item above has a matching real-world problem in the example library (github.com/SPLWare/SQLazy/tree/master/examples). If you submit your own problem, we’ll break it down, and add it to the library.