When working with Amazon Athena, SQL syntax quirks can sometimes trip up even seasoned database professionals. In this post, we’ll look at several syntax patterns that come up often when building queries in Athena.
Common Table Expressions (CTEs) with WITH
Athena supports CTEs, which allow you to define temporary result sets that can be referenced later in the query.
✅ Correct usage:
WITH cte1 AS (
SELECT column1, column2 FROM some_table
),
cte2 AS (
SELECT * FROM cte1 WHERE column1 > 100
)
SELECT * FROM cte2;
❌ Incorrect usage:
WITH cte1 AS (SELECT * FROM table1)
WITH cte2 AS (SELECT * FROM table2)
SELECT * FROM cte1 JOIN cte2 ON …;
Athena requires all CTEs to be defined under a single WITH keyword, separated by commas.
Window Functions: LAG() and ROW_NUMBER()
When you need to look at values in previous rows or assign sequence numbers, Athena provides window functions:
- ROW_NUMBER()
Assigns a unique number to each row within a partition.
- ROW_NUMBER() OVER (
- PARTITION BY employee_id
- ORDER BY start_date ASC
- ) AS row_num
- LAG()
Retrieves the value from the previous row in a partition.
- LAG(salary) OVER (
- PARTITION BY employee_id
- ORDER BY start_date ASC
- ) AS prev_salary
These functions are especially powerful when tracking changes over time or comparing current and prior values.
BETWEEN is Inclusive
In Athena, the BETWEEN keyword includes both the lower and upper bounds of the range.
WHERE event_date BETWEEN DATE ‘2021-12-01’ AND DATE ‘2021-12-31’
The query above returns rows from December 1 through December 31.
If you want to exclude the upper bound, you’ll need to switch to an explicit condition:
WHERE event_date >= DATE ‘2021-12-01’
AND event_date < DATE ‘2022-01-01’
No APPLY, Use LATERAL Instead
If you come from SQL Server, you may be familiar with CROSS APPLY or OUTER APPLY. Athena does not support APPLY. Instead, you can achieve similar functionality with a LATERAL join.
SELECT e.*, x.*
FROM employees e
CROSS JOIN LATERAL (
SELECT *
FROM salaries s
WHERE s.emp_id = e.emp_id
ORDER BY s.effective_date DESC
LIMIT 1
) x
Here, LATERAL allows the subquery to reference columns from the outer query, just like APPLY would in T-SQL.
Key Takeaways
- Use one WITH clause with multiple comma-separated CTEs.
- Use window functions like LAG() and ROW_NUMBER() to track changes or assign row order.
- Remember that BETWEEN is inclusive in Athena.
- Replace APPLY with LATERAL joins when you need correlated subqueries.
By mastering these syntax patterns, you’ll avoid some of the most common pitfalls when writing Athena queries, and make your SQL more efficient, readable, and powerful.
