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 ```