Some techniques that could be useful:
## window functions
- especially useful when dealing with time based data, such as timed events
- Docs: [SQL Window Functions | Advanced SQL - Mode](https://community.modeanalytics.com/sql/tutorial/sql-window-functions/)
## with queries / common table expressions
- kind of like extracting part of a larger computation into a named variable
- Docs: [PostgreSQL: WITH Queries (Common Table Expressions)](https://www.postgresql.org/docs/current/static/queries-with.html)
## aggregate expressions
Docs: [PostgreSQL: Documentation: 14: 4.2. Value Expressions](https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES)
```sql
-- aggregate expression
SELECT
max(energy_wh) as max_reading_wh,
max(energy_wh) filter (where interval_start_at < now() - interval '5 days') as max_reading_wh_5_days_ago
from energy_readings
```