Beyond Basic SQL
Most analysts learn SQL through SELECT statements, JOINs, and GROUP BY aggregations. These cover perhaps 60% of day-to-day work. The remaining 40% — ranking results within groups, computing running totals, comparing each row to its peers, structuring complex multi-step queries — requires a deeper feature set. This article covers window functions, common table expressions (CTEs), subqueries, and query optimization techniques that separate intermediate SQL practitioners from advanced ones.
Window Functions
A window function performs a calculation across a set of rows that are somehow related to the current row, without collapsing those rows into a single output row the way GROUP BY does. The result is appended to each row, preserving the original granularity of the data.
The syntax uses the OVER clause to define the window: function() OVER (PARTITION BY col ORDER BY col ROWS/RANGE frame).
PARTITION BY divides rows into groups (like GROUP BY), but each group is processed independently while all rows remain visible. ORDER BY within OVER determines the order in which rows are processed within each partition. The optional frame clause (ROWS or RANGE) limits which rows within the partition are included in the calculation.
Common Window Functions
Function | Description | Example Use Case |
|---|---|---|
ROW_NUMBER() | Assigns a unique sequential integer to each row within the partition | Deduplicate rows; select the most recent record per user |
RANK() | Assigns rank; ties get the same rank, next rank skips numbers | Leaderboards where ties share a rank |
DENSE_RANK() | Like RANK() but no gaps after ties | Top N per group with tied positions |
NTILE(n) | Divides rows into n approximately equal buckets | Quartile or decile segmentation |
LAG(col, n) | Returns the value of col from n rows before the current row | Month-over-month comparison; detecting changes |
LEAD(col, n) | Returns the value of col from n ruows after the current row | Next event in a sequence; churn prediction features |
SUM() OVER | Running total or sum over the partition | Cumulative revenue; running headcount |
AVG() OVER | Moving averageR�"'F�F���fW&vS��FC��FC�r�F�&��Ɩ�rfW&vS�6��&R&�rF�w&�W�V���FC���G#��G#��FC�d�%5E�d�TR����5E�d�TR����FC��FC�&WGW&�2f�'7B�"�7Bf�VR��F�Rv��F�rg&�S��FC��FC�f�'7BW&6�6RFFS���7B&V6V�B7FGW2W"66�V�C��FC���G#��G#��FC�U$4T�E�$䲂���FC��FC�&V�F�fR&�2g&7F���&WGvVV��B��FC��FC�W&6V�F��R66�&��s�F�7G&�'WF����Ǘ6�3��FC���G#���F&�G����F&�Sࠣƃ#�&7F�6�v��F�rgV�7F���W���W3���#����7G&��s�FVGWƖ6F���( B�VWF�R��7B&V6V�B&V6�&BW"W6W#���7G&��s�������6�FS�4T�T5B�e$���4T�T5B��$�u��T�$U"���dU"�%D�D���%�W6W%��B�$DU"%�7&VFVE�BDU42�2&�e$��WfV�G2�Bt�U$R&����6�FS�������7G&��s�'V���rF�F��b&WfV�VR'����F����7G&��s�������6�FS�4T�T5B���F��&WfV�VR�5T҇&WfV�VR��dU"��$DU"%����F�$�u2$UEtTT�T�$�T�DTB$T4TD��r�B5U%$T�B$�r�27V�V�F�fU�&WfV�VRe$�����F�Ǖ�&WfV�VS��6�FS�������7G&��s����F���fW"����F�6��vS���7G&��s�������6�FS�4T�T5B���F��&WfV�VR�&WfV�VR��r�&WfV�VR���dU"��$DU"%����F��2����6��vRe$�����F�Ǖ�&WfV�VS��6�FS�������7G&��s�76�v�W6W'2F�&WfV�VRV'F��W3���7G&��s�������6�FS�4T�T5BW6W%��B�F�F��7V�B��D��R�B��dU"��$DU"%�F�F��7V�B�27V�E�V'F��Re$��W6W%�7V�C��6�FS���ࠣƃ#�6�����F&�RW�&W76���2�5DW2����#���5DR�2��VBFV��&'�&W7V�B6WBFVf��VBBF�R7F'B�bVW'�W6��rF�Rt�D��W�v�&B�5DW2��&�fR&VF&�ƗG�'�'&V���r6���W�VW&�W2��F���VB�6WVV�F��7FW2��7FVB�b�W7F��r7V'VW&�W2��6�FRV6��F�W"�������7G&��s�&6�27��F����7G&��s�������6�FS�t�D�7FU���R2�4T�T5B����6V6��E�7FR2�4T�T5B���e$��7FU���R�4T�T5B�e$��6V6��E�7FS��6�FS�������V�F��R5DW2&R6W&FVB'�6���2�B6�&VfW&V�6RV6��F�W"6WVV�F��ǒ�V6�5DR�2Wf�VFVB��6RW"VW'�W�V7WF��������7BFF&6W2��B6�&R&VfW&V�6VB�V�F��RF��W2��F�R���VW'��"7V'6WVV�B5DW2�������7G&��s�&V7W'6�fR5DW3��7G&��s�&R�vW&gV�W�FV�6���F�B���w25DRF�&VfW&V�6R�G6V�b�V�&Ɩ�rG&fW'6��b��W&&6��6�FF7V6�2�&r6�'G2�f��FW"7G'V7GW&W2��"w&�&V�F���6��2�F�R&V7W'6�fR5DR�2Gv�'G2����VB'�T����âF�R�6��"�V�&W"�&6R66R��BF�R&V7W'6�fR�V�&W"�&VfW&V�6W2F�R5DR�G6V�b��7W�'FVB���7Fw&U5��5�6W'fW"�&�uVW'��6��vf��R��B��7B��FW&�FF&6W2���ࠣƃ#�7V'VW&�W2g25DW2g2����3���#��F&�R&�&FW#�#"6V��FF��s�#b"6V��76��s�##��F�VC��G#��F��&�6���F���F��W6Rv�V���F���F��&VF&�ƗG���F���F��W&f�&��6R��FS��F����G#���F�VC��F&�G���G#��FC��Ɩ�R7V'VW'���FC��FC�6���Rf��FW"�"66�"f�VR����W�6��v�RW6S��FC��FC���r��W7FVB��&BF�FV'Vr���FC��FC���&RW�V7WFVB&WVFVFǓ�FWV�G2���F�֗�W#��FC���G#��G#��FC�5DS��FC��FC��V�F��7FWG&�6f�&�F���3�&WW6VB��v�3�&VF&�ƗG�&��&�G���FC��FC䆖v���FC��FC�W7V�ǒ6�R27V'VW'��6��RD'2�FW&�Ɨ�R5DW3��FC���G#��G#��FC������FC��FC�6��&���rGv�F&�W2'��W��vV�W&�ǒf7FW7C��FC��FC��VF�V���FC��FC�&W7Bv�V������W�2&R��FW�VC��FC���G#��G#��FC�v��F�rgV�7F�����FC��FC�&�r��WfV�66W72F�vw&VvFW2v�F��WBu$�U%���FC��FC䆖v���6RV�FW'7F��C��FC��FC���&RVff�6�V�BF��6V�b֦���2f�"F�R6�R&W7V�C��FC���G#���F&�G����F&�Sࠣƃ#�VW'��F�֗�F������#���VW'�W&f�&��6R�GFW'2B66�R�VW'�F�B'V�2��"6V6��G2���&�w2��F�R#֖�WFW2���&�w2v�F��WB�F�֗�F����F�Rf����v��r&��6��W2ǒ7&�72��7B5�FF&6W2�������7G&��s�W6RU�����U�����ŕ�S���7G&��s������"FF&6W27W�'B�U����6����BF�B6��w2F�RVW'�W�V7WF�����( Bv��6���FW�W2&RW6VB�W7F��FVB&�r6�V�G2��B�W&F���6�7G2�U�����ŕ�R7GV�ǒ'V�2F�RVW'��B6��w2&V�W�V7WF���F��W2�&VBF�R��g&��F�R���W&��7B��FR�WGv&C��FV�F�g�gV��F&�R66�2�6W66����7Fw&U5��D$�R44���5�6W'fW"�2F�R��7BW�V�6�fR�W&F���2�������7G&��s��FW�7G&FVw����7G&��s����FW���6��V�����w2F�RFF&6RF�f��B�F6���r&�w2v�F��WB66���rF�RV�F�&RF&�R���FW�6��V��2F�BV"g&WVV�Fǒ��t�U$R���������B�$DU"%�6�W6W2�6���6�FR��FW�W2v�&�&W7Bv�V�F�R�VgF��7B6��V��2&RF�R��7B6V�V7F�fR���FW�W2�fRw&�FR6�7B( BF&�W2v�F�g&WVV�B��6W'G2�BWFFW26��V�B��B&R�fW"֖�FW�VB�������7G&��s�f��FW"V&Ǔ���7G&��s�ǒt�U$R6��F�F���22V&ǒ2�76�&�R��F�RVW'��W7V6��ǒ��5DW2�B7V'VW&�W2�F���Bf��FW"��F�R�WFW"VW'�v�B6�&Rf��FW&VB������W"��R�f��Bǖ��rgV�7F���2F���FW�VB6��V��2��t�U$R6�W6W2( Bt�U$R�T"�7&VFVE�B��##B6���BW6R���FW���7&VFVE�C�W6Rt�U$R7&VFVE�B$UEtTT�s##B��r�Bs##B�"�3r��7FVB�������7G&��s�f��B4T�T5B����7G&��s�6V�V7F��r��6��V��2f�&6W2F�RFF&6RF�&VBWfW'�6��V�����6�VF��r��W2��RF���B�VVB�6��V��"FF&6W2�&�uVW'��&VG6��gB�6��vf��R���'F�7V�"&V�Vf�Bw&VFǒg&��6V�V7F��r��ǒ&WV�&VB6��V��26��6RF�W�7F�&RFF6��V���'��6��V���������7G&��s�'F�F����r�B6�W7FW&��s���7G&��s���6��VBFFv&V��W6W2�F&�W2&R�gFV�'F�F���VB'�FFR�6�VW&�W2f��FW&��r��FFR&�vR��ǒ66�F�R&V�Wf�B'F�F���2��B6�W7FW&VB'���v��6&F��ƗG�6��V��2W6VB��t�U$R�B����6�W6W2��v�2f��FW"��F�R'F�F���6��V��F�f��BgV��F&�R66�2�������7G&��s�f��B6�'&V�FVB7V'VW&�W3���7G&��s�6�'&V�FVB7V'VW'�&VfW&V�6W26��V��2g&��F�R�WFW"VW'��6W6��r�BF�&R&R�W�V7WFVBf�"WfW'�&�r��F�R�WFW"VW'��&W�6R6�'&V�FVB7V'VW&�W2v�F�����2�"v��F�rgV�7F���2v�W&WfW"�76�&�R���ࠣƃ#�Gf�6VBvw&VvF������#����7G&��s�u$�U��r4UE3��7G&��s����r6��WF��r�V�F��Ru$�U%�vw&VvF���2��6��v�RVW'��f�"W���R�u$�U��r4UE2��&Vv����&�GV7B���&Vv�������&�GV6W27V'F�F�2'�&Vv����&�GV7B�'�&Vv������R��Bw&�BF�F�����R72�������7G&��s�$���U��7G&��s��26��'F��Bf�"��W&&6��6�w&�W��r6WG2( B$���U��"�2�&�GV6W2��&Vf��6��&��F���3���"�2����"������B���������7G&��s�5T$S��7G&��s�vV�W&FW2���76�&�R6��&��F���2�bF�R7V6�f�VB6��V��2( B5T$R��"�&�GV6W2��"������"���B���������7G&��s��d�B�T��d�C���7G&��s�6��RFF&6W2�5�6W'fW"�6��vf��R�7W�'B�F�fR�d�B7��F�F�&�FFR&�w2��F�6��V��2���FF&6W2v�F��WB�F�fR7W�'B�6��F�F����vw&VvF���6��WfW2F�R6�R&W7V�C��6�FS�5T҄44Rt�T�6FVv�'��trD�T�&WfV�VRT�4RT�B�2&WfV�VU���6�FS����ࠣƃ#�6������ǗF�6�GFW&�3���#��F&�R&�&FW#�#"6V��FF��s�#b"6V��76��s�##��F�VC��G#��F��GFW&���F���F��5�FV6��VS��F����G#���F�VC��F&�G���G#��FC���7B&V6V�B&V6�&BW"w&�W��FC��FC�$�u��T�$U"���dU"�%D�D���%��B�$DU"%�F��W7F�DU42��f��FW"t�U$R&����FC���G#��G#��FC�'V���rF�F���FC��FC�5T҆6��dU"��$DU"%�FFR$�u2T�$�T�DTB$T4TD��r���FC���G#��G#��FC���f��rfW&vS��FC��FC�dr�6��dU"��$DU"%�FFR$�u2$UEtTT�b$T4TD��r�B5U%$T�B$�r���FC���G#��G#��FC�W&��B��fW"�W&��B6��&�6����FC��FC��r�6�����dU"�%D�D���%�6Vv�V�B�$DU"%�FFR���FC���G#��G#��FC�W&6V�F��Rv�F���w&�W��FC��FC�U$4T�E�$䲂��dU"�%D�D���%�w&�W�$DU"%�f�VR���FC���G#��G#��FC�vFWFV7F�����6WVV�6W3��FC��FC�$�u��T�$U"��֖�W26��V��f�VRF��FV�F�g��6��G2�Bv3��FC���G#��G#��FC䆖W&&6��6�G&fW'6���FC��FC�&V7W'6�fR5DRv�F��6��"�&V7W'6�fR�V�&W"����VB'�T�������FC���G#���F&�G����F&�Sࠣƃ#�7V��'����#���v��F�rgV�7F���2�B5DW2&RF�RGv�fVGW&W2F�B��7BG&�F�6�ǒW��Bv�B�Ǘ7G26�W�&W72��5�v�F��WB&W6�'F��rF���FW&�VF�FRW��'G2�"Ɩ6F������W"6�FR�v��F�rgV�7F���2v�fR&�r��WfV�66W72F�vw&VvFW2�&涖�w2��B�V�v�&�&��r&�w2v���R&W6W'f��rFFw&�V�&�G��5DW2G&�6f�&��W7FVB�V�&VF&�RVW&�W2��F�6�V&ǒ��VB6WVV�F��7FW2�6��&��VBv�F��V�FW'7F�F��r�bVW'���2���FW���r��B'F�F����&6VBf��FW&��r�F�W6RF���2���r�Ǘ7G2F�w&�FR5�F�B�2&�F�6�'&V7B�BW&f�&��BBv&V��W6R66�R���� |
Create a free reader account to keep reading.