What should you do next to advance your career? (Plus an in-depth SQL analysis)

The hardest career question to answer

Welcome to this edition of the Data Patterns newsletter.

I’m going to experiment with a slightly different format going forward. There will be separate section for career advice, technical content and other stuff, so feel free to skip around.

Career Corner

Have you ever asked yourself “What should I focus on next in my career?”

The whole purpose of figuring out career next steps is to advance your career in ways that improve your psychological well being while also ensuring you protect your future value in the marketplace.

These are just some of the benefits of a career moat:

1. Autonomy: the feeling that you have control over your day and what projects you pursue.

2. Leverage: the feeling that the work you do is important and impactful and you’re well compensated for it.

3. Mastery: the feeling that you are really good at your craft and getting better.

4. Confidence: the feeling of knowing your skills are super valuable in the marketplace

It has taken lots of trial and error but I finally found the answer:

Build a skill stack

A skill stack is a collection of skills from disparate competencies that make up a strong moat.

Influenced by the Growth Competency Model by Adam Fishman I’m excited to present the Data Competency Model (DCM) in it’s early form (beta version 0.1). This is still a work in progress and I’m hoping that conversations here can help shape it so your feedback is appreciated.

The DCM has four quadrants:
1. Tech skills
2. Business acumen
3. Communication skills
4. Problem solving skills

I'll provide more detail in the future but for now, the core idea is if you only focus on technical skills, you’ll be too one-sided.

By developing additional competencies, like business acumen or communication skills you will guarantee the benefits I talked about above.

SQL Pattern of the Week (SQLPOW)

In this week’s section I thought I’d do something different. I found this really long SQL query on Github and wanted to do an analysis to point out patterns that I see and have covered in the book.

You can find the entire SQL query on Github here. It calculates some critical SaaS growth accounting metrics (with fake data)

This code demonstrates some very interesting patterns on how to effectively work with time-series data in SQL.

My book readers will immediately recognize the query composition pattern with CTEs breaking it up into logical, reusable components.

We have a CTE for dau (Daily Active Users) and then wau (Weekly Active Users) and mau (Monthly Active Users) based on it so you can easily read the query top to bottom.

Each of these CTEs also modifies the grain of the query from one row per user per day to one per user per week and per month.

Notice how mau_decorated joins on user_id so the granularity stays at the user level. This btw is an older style join which I don’t recommend. You should specify the keyword join to be super clear.

Next we have a monster of a CTE where a lot of stuff is happening.

First it’s doing a self full outer join on mau_decorated on the same user_id but looking at the next month. Why?

When working with time-series data and you want to compare two time periods, the easiest way to do it is through a self join. Could you have used window functions here? Probably yea, that would also be a valid solution.

Second it’s calculating some metrics by counting users in that time period. Notice that it’s using:

COUNT(DISTINCT CASE WHEN <cond> THEN user_id ELSE NULL END)

This is equivalent to COUNTIF() in Excel. Now I usually advise using this pattern for conditional counts:

SUM(CASE WHEN <cond> THEN 1 ELSE 0 END)

However when you need to count only distinct occurrences you have to use the first pattern. Notice the difference for the FALSE condition is NULL for COUNT() and 0 for the SUM()

Third this code is doing some premature sorting inside the CTE. This is a bad idea 99% of the time. Makes your query slow and costly.

I’m going to end it here so I don’t overwhelm you. I’ll continue the analysis next time.

Until then.