Day Of The Week Analysis in SQL

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

Weekday usage analysis (also known as DOW analysis — day of the week) is my favorite quick data exploration. I often run it as the first query working on a new dataset — breaking user actions down on days of the week. Also, it’s a common question during SQL interviews for beginner analyst positions.

It’s my favorite data question 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.

By the way, you are most likely to work A LOT with weekday analytics in finance — with stocks and equity markets analyzing and forecasting the “Calendar Effect” impact, understanding how Time of the Day, Day of the Week, Month of the Year affect data fluctuations and anomalies. It’s a virtual cornucopia of analysis opportunities!

Getting day of the week in SQL is easy via a CASE statement or using the DATEPART function. Here is a quick template that works in Postgres or RedShift:

SELECT CASE DATEPART(WEEKDAY,GETDATE())
WHEN 1 THEN ‘SUNDAY’
WHEN 2 THEN ‘MONDAY’
WHEN 3 THEN ‘TUESDAY’
WHEN 4 THEN ‘WEDNESDAY’
WHEN 5 THEN ‘THURSDAY’
WHEN 6 THEN ‘FRIDAY’
WHEN 7 THEN ‘SATURDAY’
END

Now, let’s use that template in a business case. Let’s say we want to see how signups fluctuate during the week. Our given table is called users, and its schema has user_id…