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.
Designing your Data Warehouse
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.
- Calculating Percentage (%) of Total Sum
- Calculating Differences from Beginning/First Row
- Calculating Top N items per Group
- Calculating Top N items and aggregating (sum) the remainder into "All other"
- Calculating Distinct & Unique items per Group
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.
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.
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.
- 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 spam, ever! Unsubscribe any time. See past emails here.