Data Analysis Journal

Share this post

Day Of The Week Analysis in SQL - Issue 39

dataanalysis.substack.com
SQL

Day Of The Week Analysis in SQL - Issue 39

How to break down user activity by day of the week in SQL

Olga Berezovsky
Apr 14, 2021
9
4
Share this post

Day Of The Week Analysis in SQL - Issue 39

dataanalysis.substack.com

Hello everyone! Today’s topic is a SQL solution for one of my favorite quick data explorations - weekday usage analysis (also known as DOW analysis - day of the week). I often run it as the first query working on a new dataset - breaking user actions down on days of the week. 

It’s my favorite because it’s simple to pull, literally takes a few minutes to write the query, and the data is easy to interpret and present. At the same time, it opens a virtual universe of insight into the business type, lifecycle, and user behavior. It helps to answer questions like: 

  • Does the business have cyclicality? 

  • If yes, what does it look like? When does the largest volume of activity happen? Is it a complete pattern? 

  • What sort of impact do holidays and weekends create? 

  • How does user acquisition work? 

  • What % of paid traffic drives signups? 

  • How do conversion funnels work? 

All of this and more. Weekday usage analysis provides a segway into exploring user activity and answering the questions above. Below, I’ll share SQL code and my approach to exploring the activity pattern.

Keep reading with a 7-day free trial

Subscribe to Data Analysis Journal to keep reading this post and get 7 days of free access to the full post archives.

Already a paid subscriber? Sign in
Previous
Next
© 2023 Olga Berezovsky
Privacy ∙ Terms ∙ Collection notice
Start WritingGet the app
Substack is the home for great writing