Table of Contents

The course is based on a story of a fictional startup company called Bindle.

Bindle is a subscription service for reading books online. The course starts with a launch of Bindle's website and together with the founders will learn how to analyze website performance, marketing performace, dive into analytics of web and mobile apps and much more.

The course covers the first year of Bindle. Each chapters covers one month focused on a specific topic. During each chapter we'll see many real life example in the life of Bindle, learn how data helps to take decisions and solve them. The most important – we'll learn all nuances of how data is recorded and stored in Bindle's data warehouse.

The course requires no prio knowledge of SQL. It'll take you from absolute 0 to Advanced with real life examples, lessons and exercises.

Introduction

You’ll learn how SQLHabit works and how to get the most out of the course. A quick chapter to set you up for an efficient and effective learning 🚀

  • 1. Introduction to SQL Habit
    Published
  • 2. A brief history of SQL and its applications
    Published
  • 3. How data is stored inside SQL database
    Published
  • 4. Query anatomy: SELECT and FROM
    Published
  • 5. Query anatomy: Comments
    Published

Fundamentals of Data Analysis

Together with Bindle’s founders you’ll learn SQL basics and how it helps to segment users (by country, time etc), work with basic timelines 📈

  • 6. Selecting all records from the table
    Published
  • 7. Sorting records
    Published
  • 8. Limiting number of records produced by query
    Published
  • 9. Filtering records from a table
    Published
  • 10. Filtering by country
    Published
  • 11. Inspecting a specific user record
    Published
  • 12. Count signups from country
    Published
  • 13. Count signups from the US
    Published
  • 14. Vanity metrics: total number of signups
    Published
  • 15. Filtering records by time
    Published
  • 16. Inclusive and non-inclusive comparison
    Published
  • 17. Combining filters
    Published
  • 18. Count signups within a time range
    Published
  • 19. Count total number of customers
    Published
  • 20. Count customers per country
    Published
  • 21. Better filtering records with ranges
    Published
  • 22. Number of customers in a month
    Published
  • 23. Using aliases for columns
    Published
  • 24. If/else logic via CASE statement
    Published
  • 25. Calculating purchase rate. Part 1
    Published
  • 26. Calculating purchase rate. Part 2
    Published
  • 27. Integer and float numbers in SQL
    Published
  • 28. Calculating purchase rate per country
    Published
  • 29. Counting number of customers per country in a month
    Published
  • 30. Count numbers of published books
    Published
  • 31. Subqueries in SQL
    Published
  • 32. Relational model of data
    Published
  • 33. Relation types: one to one, one to many, many to many
    Published
  • 34. Relational model diagrams
    Published
  • 35. Comparing dates and datetimes
    Published
  • 36. Type-casting
    Published
  • 37. Functions in SQL
    Published
  • 38. Counting books users started to read in the first month
    Published
  • 39. Measuring users activity
    Published
  • 40. Filtering NULL values
    Published
  • 41. Analyzing feature: counting users who filled their profile
    Published
  • 42. Feature acceptance criteria: percentage of users who uploaded avatar
    Published
  • 43. Counting in ranges: user age
    Published
  • 44. Fundamentals of SQL recap
    Published

Running marketing

This chapter focuses on SQL for marketing: what data can help us measure an effectiveness of our campaigns, how to group data, work with time lines and cohorts 📊

  • 45. Intro to performance marketing
    Published
  • 46. Filtering with OR operator
    Published
  • 47. Counting signups from English speaking countries
    Published
  • 48. Counting signups from DACH region
    Published
  • 49. Counting fiction books in the library
    Published
  • 50. Listing distinct book categories with DISTINCT
    Published
  • 51. Counting unique values
    Published
  • 52. Count user signup countries
    Published
  • 53. Filtering string values by matching
    Published
  • 54. Matching string values regardless of case
    Published
  • 55. First marketing campaign: counting signups from a custom domain
    Published
  • 56. Identifying email domain with SPLIT_PART
    Published
  • 57. Count unique email domains
    Published
  • 58. Сounting signups per country: intro to GROUP BY
    Published
  • 59. Order of SQL commands execution in a query
    Published
  • 60. Practicing queries on virtual datasets with UNION
    Published
  • 61. Set operators: UNION and UNION ALL
    Published
  • 62. Country with the highest number of signups
    Published
  • 63. Calculate signups per day, per country
    Published
  • 64. Referencing columns by numbers
    Published
  • 65. Most popular email domain
    Published
  • 66. The smallest book category
    Published
  • 67. Daily and hourly timelines
    Published
  • 68. Monthly and yearly timelines
    Published
  • 69. The month with the highest number of signups
    Published
  • 70. Counting number users per age group
    Published
  • 71. Biggest age group in USA
    Published
  • 72. Introduction to UTM parameters and marketing attribution
    Published
  • 73. Calculating number of signups per campaign
    Published
  • 74. Calculating number of signups from Twitter ads
    Published
  • 75. The biggest marketing campaign
    Published
  • 76. Organic vs paid signups
    Published
  • 77. Count organic signups in a month
    Published
  • 78. Introduction to referral program
    Published
  • 79. Identifying power referral users
    Published
  • 80. Best performing Twitter ad message
    Published
  • 81. Marketing metrics: ROI
    Published
  • 82. Import of marketing data
    Published
  • 83. Calculating marketing spend per channel
    Published
  • 84. Calculating marketing spend per campaign
    Published
  • 85. Finding the biggest marketing channel
    Published
  • 86. Calculate purchase rate per campaign
    Published
  • 87. Calculate purchase rate for organic traffic
    Published
  • 88. Marketing data and SQL recap
    Published

Revenue analytics and unit economics

In this chapter we’ll dive into all things revenue: calculating gross revenue, revenue per country/time period. We’ll see how we can join marketing and revenue data to calculate ROI and business unit economics (revenue per user etc) 💸

  • 89. Calculating revenue per subscription
    Published
  • 90. Calculating gross revenue per month
    Published
  • 91. Joining tables together with INNER JOIN
    Published
  • 92. Working with joined tables
    Published
  • 93. JOIN + Aggregate Functions = ❤
    Published
  • 94. Order of SQL commands execution in a query with JOINs
    Published
  • 95. Identifying the most popular book in the catalogue
    Published
  • 96. LEFT JOIN 101
    Published
  • 97. Grouping and counting with LEFT JOIN
    Published
  • 98. Calculating ROI
    Published
  • 99. Calculating marketing channel ROI
    Published
  • 100. Joining tables without conditions with CROSS JOIN
    Published
  • 101. Calculating overall performance marketing ROI
    Published
  • 102. Intro to Unit Economics
    Published
  • 103. ARPU: Average Revenue Per User
    Published
  • 104. Calculating ARPU
    Published
  • 105. Calculating ARPU per marketing campaign
    Published
  • 106. ARPPU: Average Revenue Per Paying User
    Published
  • 107. Calculating ARPPU per country
    Published
  • 108. Differentiating between past and current customers
    Published
  • 109. CPA: Cost per acquisition
    Published
  • 110. Calculating CPA per marketing campaign
    Published
  • 111. Setting default values with COALESCE
    Published
  • 112. CPC: Cost Per Click
    Published
  • 113. Detecting campaign with the highest CPC
    Published
  • 114. CAC: Customer Acquisition Cost
    Published
  • 115. Calculating CAC per campaign
    Published
  • 116. Sorting records with NULL values
    Published
  • 117. Other types of JOINs
    Published
  • 118. Calculating revenue or other metric for the last day/week/month
    Published
  • 119. Revenue analytics recap
    Published
  • 120. Revenue analytics recap. Part 2
    Published

Product Analytics. Part 1

Bindle launches a mobile app to allow users read books on the go. In this chapter we’ll learn funnel analysis for product flows (onboarding, purchase), retention metrics and more 💻

  • 121. Intro to pirate metrics AARRR: Acquisition, Activation, Retention, Revenue, Referral
    Published
  • 122. Basics of funnel analysis
    Published
  • 123. Overview of web tracking or how Google Analytics works
    Published
  • 124. How pageviews are collected and stored
    Published
  • 125. Inspecting pageviews table. SCHEMAs in SQL
    Published
  • 126. Measuring traffic per device type
    Published
  • 127. Web funnels. Part 1: Measuring traffic on a page
    Published
  • 128. Measuring unique visits on a page
    Published
  • 129. Web funnels. Part 2: Funnel based on referer URL
    Published
  • 130. Calculating funnel step churn rate
    Published
  • 131. Listing traffic sources based on referer URL
    Published
  • 132. Identifying TOP traffic source for a landing page
    Published
  • 133. How events are collected and stored
    Published
  • 134. Building web funnels based on events
    Published
  • 135. Multistep funnels with pageviews and events
    Published
  • 136. CTR of a “Signup” button
    Published
  • 137. Validation of signup events consistency
    Published
  • 138. JSON column types
    Published
  • 139. Querying JSON columns
    Published
  • 140. Counting pageviews with AB-test variation
    Published
  • 141. Cleaning data: filtering out empty values
    Published
  • 142. Percentage of mobile traffic on the page
    Published
  • 143. Signup rate per device type
    Published
  • 144. Onboarding funnel analysis on the web. Part 1
    Published
  • 145. Onboarding funnel analysis on the web. Part 2
    Published
  • 146. Onboarding funnel analysis on the web. Part 3
    Published
  • 147. Measuring activation rate
    Published
  • 148. Email signups vs Social signups
    Published
  • 149. Introduction to window functions
    Published
  • 150. Deep dive into window functions. Part 1
    Published
  • 151. How much users signup via Facebook?
    Published
  • 152. Deep dive into window functions. Part 2: Grouping and counting with percentage
    Published
  • 153. Deep dive into window functions. Part 3: Cumulative aggregates
    Published
  • 154. Popular window functions
    Published
  • 155. Calculating marketing attribution. Part 1
    Published
  • 156. Calculating marketing attribution. Part 2
    Published
  • 157. Calculating marketing attribution. Part 3
    Published
  • 158. Product Analytics. Part 1 recap
    Published

Mobile attribution

You’ll learn about mobile attribution. How it works technically and how mixed attribution model works on web and mobile 📱

  • 159. Introduction to the second part of the course
    Published
  • 160. Introduction to mobile apps
    Published
  • 161. Percentage of users with mobile app
    Published
  • 162. Recap: percentages calculation
    Published
  • 163. iPhone version distribution
    Published
  • 164. iOS version distribution
    Published
  • 165. Introduction to mobile attribution
    Published
  • 166. Intro to mobile attribution with Adjust
    Published
  • 167. Deep dive into mobile attribution data from Adjust
    Published
  • 168. Mobile attribution with Adjust
    Published
  • 169. Connecting web and mobile analytics
    Published
  • 170. Click to install conversion for website
    Published
  • 171. Install to signup conversion for website
    Published
  • 172. Overall click to signup conversion for Adjust links
    Published
  • 173. Measuring CTR of Adjust links
    Published
  • 174. Mobile ad with the highest signup rate
    Published
  • 175. Detecting broken app version with mobile attribution data
    Published
  • 176. Sorting by app’s semantic version
    Published
  • 177. Mobile attribution recap
    Published

Product Analytics. Part 2

Second part of Product Analytics focuses on analytics for mobile apps, retention rate, churn rate and LTV 📈

  • 178. Calculating release date of the app from data
    Published
  • 179. Extracting release dates from data
    Published
  • 180. Counting app releases per month
    Published
  • 181. Introduction to mobile analytics
    Published
  • 182. How to measure activation rate
    Published
  • 183. Calculating soft activation rate
    Published
  • 184. Activation rate per country
    Published
  • 185. Measuring activation rate without analytics events. Part 1
    Published
  • 186. Measuring activation rate without analytics events. Part 2
    Published
  • 187. Retention rate basics
    Published
  • 188. Calculating D7 retention rate for a daily cohort
    Published
  • 189. Aggregating analytics events into daily activity
    Published
  • 190. Calculating D1-D30 retention curve. Part 1
    Published
  • 191. Calculating D1-D30 retention curve. Part 2
    Published
  • 192. Calculating D1-D30 retention curve. Part 3
    Published
  • 193. Using helper table for date ranges
    Published
  • 194. Product Analytics. Part 2 recap
    Published

AB-tests analysis

It’s time for the new chapter at Bindle – running AB-tests. Just shipping features is not enough, AB-tests allow learning how and why changes in the product affect users behavior and happiness. This chapter talks about AB-tests analysis with SQL 💊

  • 195. Intro to AB-testing
    Published
  • 196. AB-testing vocabulary
    Published
  • 197. AB-test categorization. Part 1
    Published
  • 198. AB-test categorization. Part 2
    Published
  • 199. Building AB-test cohorts
    Published
  • 200. Calculating activation rate for AB-test variation
    Published
  • 201. Building basic AB-test funnel
    Published
  • 202. Using multiple metrics in AB-test analysis
    Published
  • 203. Calculating ARPU of AB-test variation
    Published
  • 204. Unit economics in AB-test analysis
    Published
  • 205. Purchased products of AB-test variation
    Published
  • 206. Retention in AB-test funnel
    Published
  • 207. Calculating uplift for AB-test variations
    Published
  • 208. Looking at AB-test performance in time
    Published
  • 209. Statistical significance, power and further reading on AB-testing
    Published
  • 210. AB-test analysis recap
    Published

Dashboards and alarms

This chapters talks about using SQL in BI tools, analytics dashboards and typical SQL queries behind them. We’ll also discuss automatic error detection using data and SQL. 🚨

  • 211. Intro to reporting and alarms with SQL
    Published
  • 212. Monitoring metrics for the last 24h/week/month/year
    Published
  • 213. Calculating revenue trend
    Published
  • 214. Timeline reports
    Published
  • 215. Retrospectively detecting peak in mobile signups
    Published
  • 216. Timeline reports with trends
    Published
  • 217. SQL alarms basics
    Published
  • 218. SQL alarms with threshold
    Published
  • 219. Notes on anomaly detection
    Published
  • 220. Dashboards and alarms recap
    Published

SQL at work

This chapter is a collection of tips and tricks from Marketers, Product Managers and Data Analysts that will give you a head start on using SQL at work 💼

  • 221. Overview of SQL databases: MySQL, PostgreSQL, etc
    Published
  • 222. Choosing SQL client and connecting to a database
    Published
  • 223. DataGrip – the most powerful SQL client
    Published
  • 224. Structuring filters for Ad Hoc analysis
    Published
  • 225. Queries formatting aka “developing your style”. Part 1
    Published
  • 226. Queries formatting aka “developing your style”. Part 2
    Published
  • 227. Random in SQL
    Published
  • 228. SQL at work recap
    Published

Manipulating data

In this chapter we’ll talk about everything we haven’t been talking yet – modifying data in the database. Inserting new rows, updating and deleting 🛠

  • 229. Adding records to a table
    Published
  • 230. Updating records
    Published
  • 231. Deleting records from a table
    Published
  • 232. Making subqueries available with VIEWs
    Published
  • 233. ETL: Extract, Transform, Load
    Published
  • 234. Manipulating data recap
    Published
  • 235. RIGHT JOIN
    Published
  • 236. Epilogue ❤
    Published

Appendix

This is SQL Habit’s storage room. It covers the aspects of SQL databases we haven’t discussed yet: rare JOIN types, set theory, lists of scalar, aggregate and window functions 🚚

  • 237. FULL OUTER JOIN
    Published
  • 238. Set theory basics and SQL operations: UNION, INTERSECT, EXCEPT
    Published
  • 239. Numeric functions
    Published
  • 240. Date and time functions
    Published
  • 241. String functions
    Published
  • 242. Aggregate functions
    Published
  • 243. Window functions
    Published
  • 244. NULL functions
    Published