Zach Wilson's banner

Zach Wilson

@Zachly • 51,290 subscribers

Founder @ https://t.co/CWvLDHU2Lx $80k/month | https://t.co/F5VqLpyMZn $5k/month | ADHD | 10 yrs big data experience | ex @meta, @netflix, and @airbnb

Videos

Anya Rossi

sweetdream.ai

SweetDream.ai•Sponsored•Livecam

Watch Anya Live

Anya is streaming live right now! Join her private show and enjoy exclusive content.

Exclusive private shows

1.2k viewers online

Private Show

Join now for exclusive access

Free preview available • Premium content

SQL has levels to it: - level 1 SELECT, FROM, WHERE, GROUP BY, HAVING, LIMIT Master these basic keywords and you’ll be well on your way to mastering SQL. - level 2 Mastering JOINs: Most common JOINs: INNER and LEFT Less common JOINs: FULL OUTER Joins you should avoid almost always: RIGHT and CROSS JOIN Mastering common table expressions (CTEs). The WITH keyword defines a CTE which you can imagine as a “variable” that you can query later. Using variables like this you can master algorithm techniques like recursion, breadth first search and more! CTEs also make your SQL much more readable and make your coworkers hate you less compared to nested sub queries. - level 3 Mastering window functions Window functions have 3 pieces: The function (i.e. SUM, RANK, AVG) The over clause to start the window The window definition which has 3 pieces: - how to split the window up with PARTITION BY - how to order the window with ORDER BY - how to restrict the window size with ROWS clause (useful for rolling monthly averages) Understand RANK vs DENSE_RANK vs ROW_NUMBER, I have been asked this in interviews a million times. - level 4 You understand table scans, b-tree indexes, and partitioning schemes to increase performance. Doing something like COUNT(CASE WHEN) is much better than doing multiple queries with a UNION ALL. UNION ALL is terrible for all sorts of reasons that I don’t want to get into in this post. B-trees indexes allow for efficient scanning of data in the WHERE clause. Use explain plans to understand if an index is actually being used or not! Partitioning is similar to indexes except it’s a “poor mans” index. It just keeps data in specific folders and skips the folders that don’t include the data I question. What else did I miss for mastering SQL?

SQL has levels to it: - level 1 SELECT, FROM, WHERE, GROUP BY, HAVING, LIMIT Master these basic keywords and you’ll be well on your way to mastering SQL. - level 2 Mastering JOINs: Most common JOINs: INNER and LEFT Less common JOINs: FULL OUTER Joins you should avoid almost always: RIGHT and CROSS JOIN Mastering common table expressions (CTEs). The WITH keyword defines a CTE which you can imagine as a “variable” that you can query later. Using variables like this you can master algorithm techniques like recursion, breadth first search and more! CTEs also make your SQL much more readable and make your coworkers hate you less compared to nested sub queries. - level 3 Mastering window functions Window functions have 3 pieces: The function (i.e. SUM, RANK, AVG) The over clause to start the window The window definition which has 3 pieces: - how to split the window up with PARTITION BY - how to order the window with ORDER BY - how to restrict the window size with ROWS clause (useful for rolling monthly averages) Understand RANK vs DENSE_RANK vs ROW_NUMBER, I have been asked this in interviews a million times. - level 4 You understand table scans, b-tree indexes, and partitioning schemes to increase performance. Doing something like COUNT(CASE WHEN) is much better than doing multiple queries with a UNION ALL. UNION ALL is terrible for all sorts of reasons that I don’t want to get into in this post. B-trees indexes allow for efficient scanning of data in the WHERE clause. Use explain plans to understand if an index is actually being used or not! Partitioning is similar to indexes except it’s a “poor mans” index. It just keeps data in specific folders and skips the folders that don’t include the data I question. What else did I miss for mastering SQL?

34,010 Aufrufe • vor 2 Monaten

Here’s how I would learn data engineering in 2025: 1. The basics: - learn SQL — SELECT, FROM, WHERE, GROUP BY, JOIN, HAVING, etc - learn Python — data structures: objects, arrays, tuples, namedtuples — algorithms: recursion, loops 2. Intermediate - learn distributed compute — pick up PySpark or Snowflake or BigQuery - learn data make architecture — pick up iceberg or delta lake - learn job orchestration — pick up Airflow or Mage - learn data quality — pick up Great expectations 3. Advanced - learn the data modeling techniques — one big table vs kimball vs Inmon vs data vault techniques - learn machine learning features and vector databases — pick up pinecone and how to fine tune LLMs with high quality data My newsletter has a deeper roadmap here:

Here’s how I would learn data engineering in 2025: 1. The basics: - learn SQL — SELECT, FROM, WHERE, GROUP BY, JOIN, HAVING, etc - learn Python — data structures: objects, arrays, tuples, namedtuples — algorithms: recursion, loops 2. Intermediate - learn distributed compute — pick up PySpark or Snowflake or BigQuery - learn data make architecture — pick up iceberg or delta lake - learn job orchestration — pick up Airflow or Mage - learn data quality — pick up Great expectations 3. Advanced - learn the data modeling techniques — one big table vs kimball vs Inmon vs data vault techniques - learn machine learning features and vector databases — pick up pinecone and how to fine tune LLMs with high quality data My newsletter has a deeper roadmap here:

29,396 Aufrufe • vor 1 Jahr

Keine weiteren Inhalte verfügbar