# How I Write SQL

DevFeed: [How I Write SQL](<https://devfeed.tech/articles/how-i-write-sql-41122.md>)

Original publisher: [Read original article](<https://www.craigkerstiens.com/2012/11/17/How-I-Write-SQL/>)

Author: Map

Published: 2012-11-17T20:55:56Z

Content type: tutorial

Language: en

Sources: [Craig Kerstiens](<https://devfeed.tech/sources/craig-kerstiens.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [Structured-data](<https://devfeed.tech/topics/structured-data.md>)

Tags: [arrays](<https://devfeed.tech/tags/arrays.md>), [article](<https://devfeed.tech/tags/article.md>), [development](<https://devfeed.tech/tags/development.md>), [query](<https://devfeed.tech/tags/query.md>), [readability](<https://devfeed.tech/tags/readability.md>), [schema](<https://devfeed.tech/tags/schema.md>), [sql](<https://devfeed.tech/tags/sql.md>), [table](<https://devfeed.tech/tags/table.md>)

## AI overview

A practical guide to writing clean, readable SQL using a PostgreSQL wine-rating example. It demonstrates formatting conventions, building a query incrementally, working with array tags, and using common table expressions to improve readability.

## Source excerpt

I recently got asked by a friend and former co-worker how I write SQL. At first this caught me by surprise and I assumed there was nothing different, but after a few additional comments on it, it became clear most people have no concept for creating clean readable SQL. So without further adieu here's how I write SQL, with a built up example query. First let's understand an example schema: # \dt Schema | Name | Type | Owner --------+----------------------------+-------+---------------- public | app_rating | table | craig public | app_recommendation | table | craig public | app_userprofile | table | craig public | app_wine | table | craig public | app_winemakeup | table | craig public | app_winery | table | craig public | auth_user | table | craig The above schema contains wines from wineries, that users give ratings and notes to. Especially relevant is the app_rating table, it contains a variety of things we're going to want report against: # \d app_rating Table "public.app_rating" Column | Type | Modifiers ------------+--------------------------+--------------------------------------------------------- id | integer | not null default nextval('app_rating_id_seq'::regclass) user_id | integer | not null wine_id | integer | not null rated_at | date | not null rating | integer | not null notes | text | tags | character varying(255)[] | created_at | timestamp with time zone | not null Most of the above should be pretty straightforward, though if you're unfamiliar with Arrays in Postgres check out this earlier article Given all this data lets say we want to produce some query that for a given wine contains the winery, the average rating, the tags for it. Diving in I'll typically start by creating each key part then pulling it together. Let's start with grabbing the average. But first some basic structure, for maximum readability I make sure to use all caps for reserved SQL words. For a large query I make sure all my columns/conditions are on their own line. So to get the a