/SQL

SQL Tips And Tricks

- Ben Nour tl;dr: “A somewhat opinionated list of SQL tips and tricks that I've picked up over the years in my job as a data analyst. Please note that some of these tips might not be relevant for all RDBMs.”

featured in #554

SQL Tips And Tricks

- Ben Nour tl;dr: “A somewhat opinionated list of SQL tips and tricks that I've picked up over the years in my job as a data analyst. Please note that some of these tips might not be relevant for all RDBMs.”

featured in #553

Sampling With SQL

- Tom Moertel tl;dr: “In this post, we’ll look at some clever algorithms for taking samples. These algorithms are fast and easily translated into SQL.”

featured in #546

How SQL Query Works? SQL Query Execution Order For Tech Interview

tl;dr: “While SQL queries are written in a declarative, human-readable format, there is a complex process that occurs behind the scenes to execute these queries and retrieve the desired results. In this article, we'll delve into the inner workings of SQL queries, breaking down the process step by step.”

featured in #529

How We Built Text-to-SQL At Pinterest

tl;dr: “We took the rise in availability of LLMs as an opportunity to explore whether we could assist our data users with this task by developing a Text-to-SQL feature which transforms these analytical questions directly into code.” The authors describe the tools evolution and implementation. 

featured in #507

GPT In 500 Lines Of SQL

tl;dr: "Before a text can be fed to a neural network, it needs to be converted into a list of numbers. GPT2 uses a variation of the algorithm called Byte pair encoding to do precisely that. Its tokenizer uses a dictionary of 50257 code points - in AI parlance, 'tokens' - that correspond to different byte sequences in UTF-8, plus the 'end of text' as a separate token. This dictionary was built by statistical analysis performed like this: Start with a simple encoding of 256 tokens: one token per byte. Perform the collapse 50000 times over."

featured in #478

SQL As API

- Valentin Willscher tl;dr: "I know what you are thinking: Exposing an API that accepts SQL is crazy. It's a terrible idea. Especially if the API is exposed on the internet. Doing that is insecure and will lead to SQL injection attacks, it is a nightmare to maintain and it will lock the backend implementation into a specific technology (some ANSI SQL database). But is that really true? Time to re-evaluate!"

featured in #476

The Case Of A Curious SQL Query

- Justin Jaffray tl;dr: Justin provides a deep dive into SQL's foundational aspects, highlighting the importance of a formalized approach to query behavior. Using the example of "predicate pushdown," Justin presents a SQL query that behaves differently across databases like DuckDB, SQLite, and CockroachDB. "I think it's a fun little mind bender that gives you some insight into the internals of these databases query engines without having to actually look at any code."

featured in #460

How We Built A Streaming SQL Engine

- Maor Kern tl;dr: “So you probably wake up every morning asking yourself three of life’s most pertinent questions- how do I build a streaming SQL engine, what even is a streaming SQL engine, and can our Lord drop tables owned by another user. I too found myself asking these questions, sometimes even dreaming about them- often in the form of various SQL operators pointing and laughing at my incompetence as I beg them to answer me.” A streaming SQL engine keeps queries’ results up to date without ever having to recalculate them and Maor discusses topic like data ingestion, query optimization, and stream processing.

featured in #458

SQL Join Flavors

- Anton Zhiyanov tl;dr: Anthon provides a look at SQL joins, covering types like "Qualified Join," "Natural Join," "Cross Join," "Partitioned Join," and "Lateral Join." He explains that a "Qualified join" is an umbrella term for the most common types of joins: inner, left, right, and full. "A qualified join connects records from two datasets into one, according to the matching criteria you specify," he states. The article also warns against the use of "Natural Joins," stating they are "almost always a bad idea."

featured in #450