How deep have you worked with SQL? Have you used window functions, CTEs, and do you understand the physical algorithms of JOIN?

I have worked quite extensively with SQL, including the use of window functions and CTEs (Common Table Expressions). Window functions allow performing calculations across a set of table rows that are related to the current row, without grouping the data, which is useful for analytics and complex queries.

CTEs help structure queries, making them more readable and allowing recursion.

Regarding physical join algorithms, I understand the main methods:

• Nested Loop Join — iterating over each row of one table with each row of another, efficient for small datasets or indexed tables.
• Merge Join — sorting both tables by the join key and merging them sequentially, effective for sorted data.
• Hash Join — building a hash table on one table and checking for matches from the other, works well with large datasets without indexes.

Understanding these algorithms helps optimize queries and select the right indexes.