SQL(Athena/Presto) and Python Reference Guide for Data Manipulation

This reference guide provides practical SQL examples for common data transformation tasks in Athena/Presto Syntax. To use this reference in any other SQL version, upload the code to any AI tool and ask to convert. Each section includes working queries with inline comments explaining datatypes and outputs.

For Postgres Edition, refer to this.

1. Date and Time Functions Reference

Use this code snippet to work with timestamps, dates, and time dimensions for reporting and analytics. This code converts a single timestamp into multiple time formats and dimensions. Use this to understand how to extract dates, weeks, months, quarters, and years from timestamps.

WITH time_dimensions AS (
    SELECT
        '2025-12-31T13:01:49.459Z' AS ts_iso_utc,
        1735651309.459 AS epoch_seconds,
        'Europe/London' AS timezone,
        '2025-12-31 13:01:49' AS ts_string
)
SELECT
    ts_iso_utc, -- varchar: '2025-12-31T13:01:49.459Z'
    epoch_seconds, -- double: 1735651309.459
    from_unixtime(epoch_seconds) AS ts_from_epoch, -- timestamp: 2025-12-31 13:01:49.459 UTC
    from_iso8601_timestamp(ts_iso_utc) AS ts_utc, -- timestamp: 2025-12-31 13:01:49.459 UTC
    date_trunc('second', from_iso8601_timestamp(ts_iso_utc)) AS ts_iso_utc_datetime, -- timestamp: 2025-12-31 13:01:49
    CAST(from_iso8601_timestamp(ts_iso_utc) AS date) AS utc_date, -- date: 2025-12-31
    CAST(date_trunc('week'…