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

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

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

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

Product Analytics. Part 2

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

  • 158. Introduction to mobile apps
    Coming in Nov 2019
  • 159. Percentage of users who has mobile app
    Coming in Nov 2019
  • 160. Measuring retention rate
    Coming in Nov 2019
  • 161. Retention rate for a marketing campaign
    Coming in Nov 2019
  • 162. Click to app install conversion rate
    Coming in Nov 2019
  • 163. Click to install conversion for a homepage
    Coming in Nov 2019
  • 164. Measuring click to signup conversion
    Coming in Nov 2019
  • 165. Install to signup conversion
    Coming in Nov 2019
  • 166. Measuring activation rate
    Coming in Nov 2019
  • 167. Activation rate for marketing campaign
    Coming in Nov 2019
  • 168. Activation rate in USA
    Coming in Nov 2019
  • 169. LTV: Life Time Value
    Coming in Nov 2019
  • 170. Churn rate
    Coming in Nov 2019
  • 171. Chapter 6 Recap
    Coming in Nov 2019

Launching NPS survey

NPS (Net Promoter Score) score is an industry default standard for measuring customer happiness and loyalty, great way to customers feedback and new ideas for features. In this chapter we’ll focus on analyzing raw NPS score data ⭐

  • 172. Intro to NPS (Net Promoter Score)
    Coming in Nov 2019
  • 173. Calculating overall NPS score
    Coming in Nov 2019
  • 174. NPS score per country
    Coming in Nov 2019
  • 175. NPS score over time
    Coming in Nov 2019
  • 176. NPS response rate
    Coming in Nov 2019
  • 177. NPS score for the last 30 days
    Coming in Nov 2019
  • 178. Measuring NPS response rate for the last 7 days
    Coming in Nov 2019
  • 179. Measuring NPS score uplift
    Coming in Nov 2019
  • 180. Working with sparse time lines
    Coming in Nov 2019
  • 181. Filtering empty responses from NPS
    Coming in Nov 2019
  • 182. Month 5 Recap
    Coming in Nov 2019

Running AB-tests

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 💊

  • 183. Intro to AB-testing
    Coming in Nov 2019
  • 184. Building basic AB-test funnel
    Coming in Nov 2019
  • 185. Looking at the whole AB-test funnel
    Coming in Nov 2019
  • 186. Using unit economics in AB-test analysis
    Coming in Nov 2019
  • 187. Calculate an uplift in AB-test variation
    Coming in Nov 2019
  • 188. Calculate uplift in total proceeds for AB-test variation
    Coming in Nov 2019
  • 189. Looking at AB-test performance in time
    Coming in Nov 2019
  • 190. Calculating statistical significance via SQL query
    Coming in Nov 2019
  • 191. Further reading on AB-testing
    Coming in Nov 2019
  • 192. Month 6 Recap
    Coming in Nov 2019

Analyzing search & recommendations

At this moment Bindle has thousands of books available. This chapter focuses on content: analyzing search and recommendations 📚

  • 193. Calculating search CTR
    Coming in Nov 2019
  • 194. Measure search CTR per platform
    Coming in Nov 2019
  • 195. Search efficiency
    Coming in Nov 2019
  • 196. Counting empty search queries
    Coming in Nov 2019
  • 197. Measuring search to content interaction rate
    Coming in Nov 2019
  • 198. Monitoring search performance with time
    Coming in Nov 2019
  • 199. Most popular search term
    Coming in Nov 2019
  • 200. Correlation between search and purchase
    Coming in Nov 2019
  • 201. Purchase rate for users who found a book via search
    Coming in Nov 2019
  • 202. Month 7 Recap
    Coming in Nov 2019

Monitoring and alarms

At this stage Bindle has many thousands of users and big team working on the product. It’s inevitable that some mistakes slipped in and only data could reveal them. In this chapter we’ll dive into using SQL for monitoring and setting up alarms 🚨

  • 203. Intro to monitoring with SQL
    Coming in Nov 2019
  • 204. Monitoring metrics for the last 24h/week/month/year
    Coming in Nov 2019
  • 205. When payment provider on the web was broken?
    Coming in Nov 2019
  • 206. Feature and user behavior monitoring
    Coming in Nov 2019
  • 207. When did we ship broken onboarding?
    Coming in Nov 2019
  • 208. Monitoring metrics with thresholds
    Coming in Nov 2019
  • 209. When App Store link was broken?
    Coming in Nov 2019
  • 210. Monitoring error rate
    Coming in Nov 2019
  • 211. When did we ship error on a checkout page?
    Coming in Nov 2019
  • 212. Monitoring 404 errors on the web
    Coming in Nov 2019
  • 213. When did we change book URLs but forgot to add redirect?
    Coming in Nov 2019
  • 214. Monitoring 500 errors on the web
    Coming in Nov 2019
  • 215. When website was down?
    Coming in Nov 2019
  • 216. Notes on anomaly detection
    Coming in Nov 2019
  • 217. Month 8 Recap
    Coming in Nov 2019

Analyzing content interactions

Bindle’s mission is to build the best place to find and read books. This chapter will focus on content analytics based on user engagement and star rating 🔎

  • 218. Sorting books by popularity
    Coming in Nov 2019
  • 219. What category is the most popular?
    Coming in Nov 2019
  • 220. Weekly usage pattern
    Coming in Nov 2019
  • 221. Most active day of the week
    Coming in Nov 2019
  • 222. Daily usage pattern
    Coming in Nov 2019
  • 223. Most active hour of the day
    Coming in Nov 2019
  • 224. Estimate time to finish a book
    Coming in Nov 2019
  • 225. Average time to read “War and Piece”
    Coming in Nov 2019
  • 226. Measuring social shares traffic
    Coming in Nov 2019
  • 227. The most shared book on Bindle
    Coming in Nov 2019
  • 228. Measuring DAU and MAU
    Coming in Nov 2019
  • 229. Measuring MAU uplift
    Coming in Nov 2019
  • 230. Month 9 Recap
    Coming in Nov 2019

Email communication

In this chapter we’ll deep dive into Bindle’s email communication: newsletter and promotion email metrics, web funnels and more 📧

  • 231. Measuring traffic from email campaign
    Coming in Nov 2019
  • 232. Measuring traffic from a sale campaign
    Coming in Nov 2019
  • 233. Calculating purchase rate for email campaign
    Coming in Nov 2019
  • 234. Calculating Black Friday revenue
    Coming in Nov 2019
  • 235. Measuring email opt-in rate
    Coming in Nov 2019
  • 236. Email opt-in rate of a newsletter
    Coming in Nov 2019
  • 237. Email to content interaction rate
    Coming in Nov 2019
  • 238. AB-testing emails
    Coming in Nov 2019
  • 239. Measuring performance of new newsletter headline
    Coming in Nov 2019
  • 240. Month 10 Recap
    Coming in Nov 2019

Analyzing business as a whole

In this chapter we’ll focus on analyzing business as a whole and try to answer questions like “Are we profitable?”, “When are we going to be profitable?”, “What’s our run rate?” etc 💰

  • 241. Calculating gross revenue
    Coming in Nov 2019
  • 242. Calculating total revenue per month
    Coming in Nov 2019
  • 243. Organic vs paid revenue
    Coming in Nov 2019
  • 244. Marketing ROI
    Coming in Nov 2019
  • 245. Applying payment provider fees
    Coming in Nov 2019
  • 246. Counting fees
    Coming in Nov 2019
  • 247. Calculating burn rate
    Coming in Nov 2019
  • 248. Finding referring articles in traffic sources
    Coming in Nov 2019
  • 249. TOP referral article about Bindle
    Coming in Nov 2019
  • 250. Month 11 Recap
    Coming in Nov 2019

Appendix & further reading

So far we’ve been reading (SELECT-ing) data from Bindle’s database. To be fully ready for using SQL in real life we’ll learn how to create/update/delete data in the database and more. We’ll also define next steps for you to continue maintaining and developing your SQL Habit further ❤

  • 251. SQL databases
    Coming in Nov 2019
  • 252. Creating records in a table
    Coming in Nov 2019
  • 253. Updating records in a table
    Coming in Nov 2019
  • 254. Deleting records from a table
    Coming in Nov 2019
  • 255. RIGHT JOIN
    Coming in Nov 2019
  • 256. FULL OUTER JOIN
    Coming in Nov 2019
  • 257. Creating tables in a database
    Coming in Nov 2019
  • 258. Creating schemas in a database
    Coming in Nov 2019
  • 259. Improving query performance with indexes
    Coming in Nov 2019
  • 260. Making subqueries available for everyone with VIEWs
    Coming in Nov 2019
  • 261. Set theory basics and SQL operations: UNION, INTERSECT, EXCEPT
    Coming in Nov 2019
  • 262. Numeric functions
    Coming in Nov 2019
  • 263. String functions
    Coming in Nov 2019
  • 264. Date functions
    Coming in Nov 2019