Learn Databricks SQL - Part 1 - Why, and the data we'll use

A small bike rental in Lucerne has a table with 30 rentals. What was the most recent rental of every customer?

SELECT
  customer_id,
  rental_id,
  started_at,
  price_chf
FROM rentals
QUALIFY row_number() OVER (PARTITION BY customer_id ORDER BY started_at DESC) = 1
ORDER BY customer_id

One statement, and no subquery. QUALIFY filters on a window function the way WHERE filters on a column. Without it, the window function moves into a subquery and the filter outside:

SELECT
  customer_id,
  rental_id,
  started_at,
  price_chf
FROM (
  SELECT
    customer_id,
    rental_id,
    started_at,
    price_chf,
    row_number() OVER (PARTITION BY customer_id ORDER BY started_at DESC) AS rn
  FROM rentals
) AS ranked
WHERE rn = 1
ORDER BY customer_id

I ran both. They return the same ten rows.

Two rows of the result are odd. The last rental of customer 3 has no price. And customer 99 has a rental, but the bike rental has no customer 99. Both are on purpose, and this part explains why.

This series goes through the SQL of Databricks like this: statement by statement, every one run on the same small tables, every one with what came back.

Why this series

Databricks documents its SQL in the SQL language reference. On 11 November 2026, its index linked to about 200 pages of statements and clauses, and the built-in functions are a long list of their own.

I write SQL on Databricks all the time. And most of the time, I write the same few statements, the same way, again and again. They work, so I never look further. Most of those 200 pages I have never opened.

This series is my reason to open them. I want to work through the reference properly, try the statements I never use, and find out what the ones I use every day can do that I don’t know about. It’s practice for me, first of all.

Writing it down forces me to be exact: an example in an article has to run, and I have to understand why it returns what it returns. And if I write it down anyway, someone else can use it too.

The reference itself doesn’t make that easy. It’s complete, and it’s a reference: syntax, parameters, and examples on tables that change from page to page. It tells me what a statement accepts. I want to see what it does – with every clause and every option, on data I know, including the cases where it fails.

So the rules of the series are:

• One topic per part. A part covers one statement or one clause, with every variation the reference describes.
• Everything is run. Every statement in an article ran on a SQL warehouse. The result tables are generated from what the warehouse returned. I don’t type results.
• Errors are examples too. If something doesn’t work, the article shows the statement and the error message.
• The same data everywhere. Six small tables. After a few parts you know every row.
• What I don’t know, I say. If I couldn’t test something, the part says so.

What “Databricks SQL” means here

The reference calls itself “a SQL command reference for Databricks SQL and Databricks Runtime”. Databricks SQL is the part of the platform with the SQL warehouses and the SQL editor. Databricks Runtime is what runs on clusters.

In this series, Databricks SQL means: SQL sent to a SQL warehouse. That’s where I ran every example. Most of the language is the same on both sides, and the pages of the reference say where a statement applies and from which version. The page of the QUALIFY clause from above, for example, says Databricks SQL and Databricks Runtime 10.4 LTS and above. Every part has a section “Where it runs” with these notes.

What you need

A Databricks workspace with a SQL warehouse. Databricks Free Edition is enough: it costs nothing and comes with one SQL warehouse, limited to the size 2X-Small. It has a quota. If you exceed it, the compute is shut down for the rest of the day, and the data stays (Free Edition limitations).

I run the series on a Free Edition workspace. I don’t know whether every statement runs there. Where one doesn’t, the part says so.

The data

The data is a fictional bike rental in Lucerne, in a schema called velo in the catalog sql_playground. Six tables, the largest with 30 rows:

Read a line from one end to the other: a bike has zero or many rentals, a rental has exactly one bike. A rental has exactly one start station, but zero or one end station, because a rental that is still running has none. price_changes has only a dashed line: a bike finds its prices by its type and the date, and bike_type alone is not a key there.

The keys are the intended ones. The tables declare no primary or foreign keys, so nothing stops a rental from pointing to a customer that doesn’t exist. One does.

The tables are small so that every row fits on the screen. When a join drops a row or a filter loses a NULL, you can see which one.

And some rows are wrong, missing or odd on purpose. Real data has such rows, and they are where SQL gets interesting. Here are all six tables, with the rows to remember.

stations

SELECT *
FROM stations
ORDER BY station_id

• The rental is fictional, the places are real. Every station has the coordinates of the public transport stop of the same name in Lucerne, from the stop search of transport.opendata.ch, and the postal code at that point, from the official index of localities of swisstopo.
• Station 6, Littau, opens on 15 April 2026, after the week of the rentals. It has no capacity yet, no bikes and no rentals.

bikes

SELECT *
FROM bikes
ORDER BY bike_id

• Bike 11 is in maintenance and has no home station.
• Bike 12 was bought on 30 March 2026 and has never been rented.
• Bike 7 is retired.

customers

SELECT *
FROM customers
ORDER BY customer_id

• Customers 4 and 9 are the same person, signed up twice. The name is in a different case, the email too, and the second email has a space before and after it.
• Customer 5 has no email, customer 7 no plan.
• Customer 8 is a company and has no first name.
• The city Luzern is spelled three ways: Luzern, luzern and LUZERN.
• Customer 10 signed up and never rented a bike.

price_changes

SELECT *
FROM price_changes
ORDER BY valid_from, bike_type

A rental costs the unlock fee plus the price for every started 30 minutes. The prices went up on 28 March 2026, in the middle of the week of the rentals. Which price a rental gets depends on the day it started.

rentals

SELECT *
FROM rentals
ORDER BY rental_id

One week, 23 to 31 March 2026.

• Rental 16 belongs to customer 99. There is no customer 99.
• Rental 18 starts before midnight and ends after it.
• Rental 21 was free: a promotion. The price is 0, and the payment method is NULL. 0 and NULL are not the same thing, and this row has both.
• Rental 23 crosses the switch to summer time. More about it below.
• Rental 28 took exactly 30 minutes: one started half hour, or two?
• Rental 30 is still running. It has no end, no distance and no price.

rentals_raw

SELECT *
FROM rentals_raw
ORDER BY raw:rental_id::int

The same 30 rentals as the app would send them: one JSON document per rental, in a single string column. The documents are nested, and they have two things the table doesn’t: a list of tags, sometimes empty, and details about the app.

Time

All timestamps in the tables are UTC. How a timestamp is displayed depends on the time zone of the session. On my warehouse, that’s UTC too:

SELECT current_timezone() AS session_time_zone

Lucerne is not in UTC. Its time zone is Europe/Zurich: Central European Time (CET), one hour ahead of UTC, in winter, and Central European Summer Time (CEST), two hours ahead, in summer. That makes two rentals interesting. Here they are in UTC and in local time:

SELECT
  rental_id,
  started_at,
  ended_at,
  from_utc_timestamp(started_at, 'Europe/Zurich') AS started_local,
  from_utc_timestamp(ended_at, 'Europe/Zurich') AS ended_local
FROM rentals
WHERE rental_id IN (18, 23)
ORDER BY rental_id

Rental 18 starts on 27 March in UTC and on 28 March in Lucerne. 28 March is the day the prices went up. Which day counts is a decision, and the data has to make it somewhere. I calculated the prices with the UTC date.

Rental 23 started at 01:50 local time, in CET, and ended at 03:20, in CEST. It took 30 minutes. In that night, the clocks in Switzerland went from 02:00 to 03:00.

Get the data

Everything is in a public repository: github.com/stefanko-ch/learn-databricks-sql. One script, setup/00_velo.sql, creates a catalog of its own, sql_playground, the schema velo in it, and the six tables with CREATE OR REPLACE TABLE and INSERT ... VALUES. So the data is in the same place for everyone: sql_playground.velo. The rows are written out in the script. Nothing is random, so you get exactly the rows above. And because of CREATE OR REPLACE, running it again resets the data.

This is how I ran it, in a Free Edition workspace with nothing in it.

1. Get the repository into the workspace. In the sidebar, open Workspace, then Create > Git folder. Paste the URL of the repository. Databricks fills in the provider and the folder name.

A few seconds later the repository is a folder in your workspace. The repository is public: I had no Git credentials set up in the workspace, and it didn’t ask for any.

2. Open the script and choose the SQL warehouse. Open setup, then 00_velo.sql. The file opens in the SQL editor. Look at the compute selector at the top right before you run it: for me, the file was attached to “Serverless”, not to a SQL warehouse. Open the selector and choose your warehouse. On Free Edition there is one, the Serverless Starter Warehouse.

3. Run it. Click the Run button, or press Cmd/Ctrl + Enter. “Run all statements” was already ticked in the menu next to the button. The script has 16 statements. The first time they took 22 seconds; the screenshot is from a second run, with 16. The last result is the 30 rows inserted into rentals_raw:

4. Look at it. In the sidebar, open Catalog. There is a new catalog sql_playground with the schema velo and its six tables.

The screenshot already shows a second schema, kino. It comes from a second script, for the exercises at the end of this part.

Every example in the series starts in sql_playground.velo. In a new session, one statement gets you there:

USE sql_playground.velo;

Then check that you have what I have:

SELECT 'bikes' AS table_name, count(*) AS row_count FROM bikes
UNION ALL
SELECT 'customers', count(*) FROM customers
UNION ALL
SELECT 'price_changes', count(*) FROM price_changes
UNION ALL
SELECT 'rentals', count(*) FROM rentals
UNION ALL
SELECT 'rentals_raw', count(*) FROM rentals_raw
UNION ALL
SELECT 'stations', count(*) FROM stations
ORDER BY table_name

How the parts are built

Every part has the same sections:

1. A question to the bike rental, and a figure: the table before, the result after.
2. The short version: the syntax in a few lines, with a link to the page in the reference.
3. Examples, from simple to surprising. One sentence, the SQL, the result.
4. Traps: what goes wrong, with the example that shows it.
5. Where it runs: the date and the version I tested on, and the minimum versions the reference gives.
6. Covered in this part: which pages of the reference the part covers, and what it leaves for other parts.

The SQL is formatted the same way everywhere: keywords in upper case, every clause on its own line.

What this series is not

• Not a replacement for the reference. Every part links to the pages it covers. The grammar is there.
• Not a performance course. It’s about what statements do, not about tuning a warehouse.
• Not about PySpark. Only SQL.

Exercises

Every part of this series ends with a few exercises. They don’t use the bike rental: you have seen those answers in the article. They use a second small dataset, a cinema in Lucerne with five tables: movies, screens, screenings, tickets and members, in the schema sql_playground.kino. It’s as made up as the bike rental, and it has its own odd rows.

A second script creates it: setup/01_kino.sql. Run it the same way as the first one. It has 14 statements, and for me it took 17 seconds.

I don’t publish solutions. Every exercise shows what should come out, so you can check yourself.

1.1 Run the script, then count the rows of the five tables. You should get:

1.2 Look at every table with SELECT *. The cinema has rows that are missing something, wrong or odd, like the bike rental. I put in more than ten. How many do you find? The movies table, to start with:

1.3 The diagram says: a ticket has exactly one screening. That’s the intention, and nothing in the tables enforces it. Is it true for every ticket? Look at tickets and screenings. You should find one ticket that breaks it.

1.4 Take the query from the top of this part and change it: the most recent ticket of every member. If two tickets were sold at the same time, take the one with the higher ticket_id. You should get eight rows, and the first one is not a member:

Where it runs

I ran everything in this part on the Serverless Starter Warehouse of a Databricks Free Edition workspace on 11 November 2026; current_version() reported DBSQL version 2026.44.

The code

The setup script and all statements of this part, with the results as they came back, are in the repository: learn-databricks-sql.