Find each student's previous year's scores using the LAG function.

To find each student's previous year's score, use the LAG window function with PARTITION BY student_id and ORDER BY year. This calculates the score from the preceding row within each student's ordered set of records.

Mechanics and "Why"

The LAG function retrieves a value from a row at a given physical offset before the current row within its partition. Its full syntax is LAG(expression, offset, default).
* expression: The column whose value you want to retrieve (e.g., score).
* offset: An integer indicating how many rows back to look. The default is 1. To find the score from two years back, you would use LAG(score, 2).
* default: The value to return if the offset goes beyond the start of the partition. The default is NULL. This means the first record for each student will have a NULL prev_year_score. You can use COALESCE(LAG(score), 0) to replace NULL with 0, for example.

The OVER clause is crucial for defining the window:
PARTITION BY student_id: This divides the dataset into independent groups, ensuring the LAG calculation only considers scores for the same* student. Without this, LAG would look at the previous row in the entire dataset, potentially a different student.
* ORDER BY year: This defines the logical order within each student's partition, so "previous year" is correctly identified by the chronological sequence of years.

Example and Trade-offs

SELECT
    student_id,
    year,
    score,
    LAG(score, 1, 0) OVER (PARTITION BY student_id ORDER BY year) AS prev_year_score
FROM
    student_scores;

Window functions, especially those with PARTITION BY on large datasets, can be computationally intensive. In distributed systems like Spark or Snowflake, they often require data to be grouped and sorted across nodes (known as a data shuffle), which can significantly impact query performance. Optimizing the PARTITION BY key and ensuring efficient data distribution are key considerations for performance.

In the interview, also mention…

Discuss related window functions like LEAD (for subsequent values), ROW_NUMBER, RANK, or NTH_VALUE to demonstrate a broader understanding of analytical functions and their applications in data engineering.