Advanced SQL Recipes to jump start your Analysis

This is a compendium of Advanced SQL tricks meant to serve as a reference for data analysts. There's no particular order to reading these recipes – each one starts with an applicable business case. Start with the most pressing business decision you are facing, and solve it by using the SQL recipe as a template.

Smoothing Data

Raw data is inherently noisy and presenting it as-is can be distracting. Use different types of moving averages to present a streamlined and consistent story.

👋 Updated every week

We are open sourcing everything from the experience working with our agency clients. They spend thousands of dollars to get this level of detailed analysis – which you can now get for free.

We send one update every week. Join 400+ data analysts who are leveling up with our recipes. 👊

Unsubscribe any time. See past emails here.

Calculations Per Group

Key business decisions are made based on comparisons across various product, customer and employee groups. Use SQL window functions to calculate sales compensation plans and quotas, year-to-date comparisons and hot selling products across categories.

Growth Rates

Growth rates are present in every business report. Model month-over-month and exponential growth rates and Pareto charts in order to compare and focus your efforts.

Summarizing Data

When you get your hands on a dataset, you'll want to quickly get a feel for the data. Looking at the shape of the data, bucketing into groups, finding outliers, calculating relationships and correlations lets you do that.

Ranking your Best and Worst Customers

Understanding your best and worst customers is key to profitable growth. Use lead-scoring and net promoter score surveys to rank your customers.

Forecasting & Predicting the future

Accurate forecasting of future activity is incredibly useful when provisioning resources and maintaining sufficient lead time.

SQL for marketing

Marketing teams are overwhelmed with data. Understand return on ad spend, attribute revenue to marketing programs and payback periods of different marketing channels.

Database compatibility

Though SQL may be universally supported, there are subtle differences in compatibility across various analytical databases. Some handy guides on using the features that may or may not be natively supported on your analytical database.

Data Cleansing (aka "Wrangling")

Dirty data can lead you astray. Understand pattern matching (eg: business emails), filling missing data, removing duplicates and empty values to sufficiently deal with messy data.

Coming Soon

  • Comparing Means with Statistical Testing
  • Calculating Medians
  • Calculating Fractional and Ordinal Rank
  • Calculating n-grams
  • Calculating Funnel drop-off metrics
  • Cohort charts for Retention Analysis
  • Understanding EXPLAIN ANALYZE
  • Pivoting and Unpivoting data
  • Intrusion Dection with IP addresses
  • GIS/Spatial queries
  • Correlated and Uncorrelated subqueries
👋 No fuss, just SQL We are open sourcing everything from the experience working with our agency clients. They spend thousands of dollars to get this level of detailed analysis – which you can now get for free. We send one update every week. Join 400+ data analysts who are leveling up with our recipes. 👊

No spam, ever! Unsubscribe any time. See past emails here.

results matching ""

    No results matching ""