Blog /

Six Tricks For Writing Complex SQL Queries

Eric Sammer

SQL is incredibly powerful, but it can be hard to remember what output you get from different parts of a SQL query. Here's my mental cheat-sheet for writing and reading non-trivial SQL queries.

The Tricks

1. The absence of 'group by' means you will get one output record per input, after predicates like 'where' clauses are applied.

2. If a query has a 'group by', you will get one record per group.

3. 'Over' clauses generate a field for each output record. They operate on a window defined by either a) the 'partition by' expression - which works like a 'group by' - or b) a range of records defined by 'rows between'.

4. Subqueries can be put in a bunch of different places in SQL. They can also appear in predicates in the 'where' clause, where their output acts as part of the filtering condition.

5. In a select list, a subquery (usually) runs once, before the outer query, and its output is inserted as a field.

6. In a 'from', a subquery's output acts as an input table. You can alias the subquery and reference its fields.

Here are a few examples of SQL tricks demonstrating these tricks, written for clarity not performance:

Example: Tweet Likes / All Likes

Example: Current Order Price vs Average Over The Last 10

Example: HTTP Errors By Path And Status, And By Path

There are lots (and lots) of intermediate SQL bits that can greatly simplify life. I've oversimplified a bunch of things and focused on some common cases. Performance can vary greatly based on the DBMS, data, indexes, and config, but hopefully, this helps! Join me on the Decodable community slack with questions, suggestions or comments!

You can get started with Decodable for free - our developer account includes enough for you to build a useful pipeline and - unlike a trial - it never expires.

Learn more:

Join the community Slack

Demo Day - Intro to Decodable and the new UX

Recorded from the Demo Day on March 10th 2022, Decodable CEO Eric Sammer demonstrates the capabilities of the real-time data engineering platform through the new user experience.

Learn more

Streaming Data Pipelines with SQL

Streaming data systems have been growing more capable and flexible over the past few years. Despite this, it is still challenging to build reliable pipelines for stream processing. In this Data Engineering Podcast hosted by Tobias Macey, Eric Sammer, discusses the shortcomings of the current set of streaming engines and how they force engineers to work at an extremely low level of abstraction. He explains how Decodable addresses that limitation and lets data engineers build streaming pipelines entirely in SQL.

Learn more

The Top 5 Streaming ETL Patterns

ETL and ELT are traditionally scheduled batch operations, but as the need for always-on, always-current data services becomes the norm, realtime ELT operating on streams of data is the goal of many organizations - if not the reality, yet.In real world usage, the ‘T’ in ETL represents a wide range of patterns assembled from primitive operations. In this blog we’ll explore these operations and see examples of how they’re implemented as SQL statements.

Learn more

This Microservice Should Have Been a SQL Statement

In event-driven architecture, functions can be used to replace backend microservices. In this blog, we will demonstrate how to use Decodable as a serverless function written in SQL performing stateful transformations, replacing unnecessary microservices in your application. "That microservice could have been a SQL statement" made real, with Decodable.

Learn more


Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse varius enim in eros elementum tristique. Duis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere.

Learn more
Pintrest icon in black

Start using Decodable today.