Day Of The Week Analysis in SQL - Issue 39

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

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.