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.