How advance do data science and ML interviewers expect your SQL to be, for a junior position? For example, what other topics do we need to tackle after - SELECT - Aggregates (COUNT, SUM, MIN, MAX, DISTINCT, GROUP BY, HAVING) - JOIN, UNION - Subqueries (IN, EXISTS, correlated) Is CTE, WITH important to prepare for?
It’s the analytical thinking that matters. Rest all can be googled.
Analytical / window functions are sometimes asked. Eg partition by, over
PhD?
Yes I have one
For DS just syntax of SQL... For ML rarely SQL, just LC plus AI knowledge.
Is BI the role that requires more SQL during interviews? Or is that SWE?
For BI , DE role heavy SQL is must. (LC all db problems will help). SwE you can solve the problem with any language unless the role expects you to do production level coding.
Know how to work with dates. CASE WHEN, IF, WITH etc
What you have listed is sufficient IF you can properly apply them to solve problems.