Q: SQL - How X in SELECT X, AGG(Z) OVER (PARTITION X) is handled in analytics SQL Question
In SELECT SQL using analytics function(s)
SELECT 
    X, AGG_FUNCTION(Z) OVER (PARTITION X ORDER BY Y) 
FROM 
    T

how is the column X in the SELECT corelated to X in the PARTITION BY clause?
Example table
Name                  Null?    Type           
--------------------- -------- -------------- 
STUDENT_ID            NOT NULL NUMBER(8)      
SECTION_ID            NOT NULL NUMBER(8)      
GRADE_TYPE_CODE       NOT NULL CHAR(2)        <--- This is X column
NUMERIC_GRADE         NOT NULL NUMBER(3)      

Excerpt of the table
SELECT DISTINCT
    student_id,
    section_id,
    grade_type_code,
    numeric_grade
FROM
    grade;


In the example below, the grade_type_code is the X column. Apparently grade_type_code is related to the grade_type_code in PARTITION BY clause.
query = """
SELECT DISTINCT
    grade_type_code,   <---- How this 'grade_type_code' is related to that in PARTITION BY clause?
    MAX(numeric_grade) OVER (PARTITION BY grade_type_code) as maximum,
    MIN(numeric_grade) OVER (PARTITION BY grade_type_code) as minimum,
    ROUND(AVG(numeric_grade) OVER (PARTITION BY grade_type_code),2) as mean,
    ROUND(VARIANCE(numeric_grade) OVER (PARTITION BY grade_type_code),2) as var,
    ROUND(STDDEV(numeric_grade) OVER (PARTITION BY grade_type_code),2) as std,
    COUNT(numeric_grade) OVER (PARTITION BY grade_type_code) as cnt
FROM
    grade
ORDER BY
    grade_type_code
"""
spark.sql(query).show(truncate=False)

+---------------+-------+-------+-----+-----+----+---+
|grade_type_code|maximum|minimum|mean |var  |std |cnt|
+---------------+-------+-------+-----+-----+----+---+
|FI             |99     |76     |85.77|46.99|6.86|205|
|HM             |99     |70     |86.12|67.27|8.2 |798|
|MT             |99     |76     |87.08|43.15|6.57|204|
|PA             |99     |70     |86.32|56.43|7.51|394|
|PJ             |99     |76     |87.0 |48.2 |6.94|21 |
|QZ             |99     |73     |86.75|59.97|7.74|382|
+---------------+-------+-------+-----+-----+----+---+

I believe that something similar to correlated subquery is happening inside the SQL engine. The SQL engine finds grade_type_code column in SELECT statement, and check if it exists in the PARITION BY clause. If exists, the value of the grade_type_code is applied in the PARTITION BY part.
Because when tested the same using the GROUP BY SQL below, the result matches. The aggregation functions work on respective group created by GROUP BY grade_type_code specific to a grade_type_code value. I believe the same happens with SELECT X, AGG_FUNCTION(Z) OVER (PARTITION X ORDER BY Y) FROM T.
query = """
SELECT 
    grade_type_code,     <----- column X
    MAX(numeric_grade) as maximum,
    MIN(numeric_grade) as minimum,
    ROUND(AVG(numeric_grade),2) as mean,
    ROUND(VARIANCE(numeric_grade), 2) as var,
    ROUND(STDDEV(numeric_grade), 2) as std,
    COUNT(*) as cnt
FROM
    grade
GROUP BY
    grade_type_code
ORDER BY 
    grade_type_code
"""
spark.sql(query).show(truncate=False)

+---------------+-------+-------+-----+-----+----+---+
|grade_type_code|maximum|minimum|mean |var  |std |cnt|
+---------------+-------+-------+-----+-----+----+---+
|FI             |99     |76     |85.77|46.99|6.86|205|
|HM             |99     |70     |86.12|67.27|8.2 |798|
|MT             |99     |76     |87.08|43.15|6.57|204|
|PA             |99     |70     |86.32|56.43|7.51|394|
|PJ             |99     |76     |87.0 |48.2 |6.94|21 |
|QZ             |99     |73     |86.75|59.97|7.74|382|
+---------------+-------+-------+-----+-----+----+---+

Please advise if my understanding is correct, and provide pointers to resource which explain this mechanism in detail if any.

A: The analytical or window functions do not require column from "partition by" part to present in the "select" part of the statement below.
Here are some examples in the documentation
The "partition by" just says to the function: "now, start calculating sum (avg, count, whatever) starting from 0 for that group ".
There are 2 reasons why you get same results with analytical and group functions:

*

*there are only analytical function in the select with same partition by criteria. This will result identical rows for every value of grade_type_code (for example, there will be 3 identical rows for grade_type_code = QZ)

*there is the "distinct" which eliminates duplicate rows

