Compare With Previous Row

Write a SQL query using window functions to compare each row with the previous row in the same partition.

Use the observations table. Within each partition_key, rows are ordered by observed_at and then observation_id.

Output

1. Return one row per observation with partition_key, observation_id, observed_at, metric_value, previous_metric_value, and difference_from_previous.
2. The previous value and difference should be NULL when no previous row exists or either value needed for subtraction is NULL.
3. Order the result by partition_key with NULL partitions last, then observed_at, and observation_id.