Visualization of Spotify dataset

A new detail that helps you step up your ‘window’ game in SQL!

I have a love-hate relationship with the window functions in SQL. I love how it allows one access to data from rows beyond the current row as it simplifies the tasks like creating rolling averages or ranking values within a group. I hate it when I have to write a complicated query involving the usage of window functions along with joins, subqueries, CTEs, etc. But I learned a new detail recently about window functions that made me gasp and admire it. To understand it, let us consider the task of creating a 3-day rolling average on a numeric column. This task requires data from the current row and the preceding two rows. Hence, it is a no-brainer to use window functions here. The usual syntax of the window function for averaging is as below: AVG() OVER () This window function considers the value of the current row and the value of all the preceding rows to calculate and display the average. (Let us overlook the functionalities of PARTITION BY and ORDER BY clauses for a while.) But, to

Solving Customer Churn with a hammer!

Learning when data should take a back seat and give way to domain knowledge is a valuable skill. Suppose you built a machine learning model on the data of your customers to predict churn risk. Now that you have a risk score for each customer, what do you do next? Do you filter the top n% based on the risk and send them a coupon with a discount in the hopes that it will prevent churn? But what if price is not the factor driving churn in many of these customers? Customers might have been treated poorly by customer service, which drove them away from your company's product. Or there might have been an indirect competitor's product or service that removes the need for your company's product altogether (this happened to companies like Blockbuster and Kodak in the past!) There could be a myriad of factors, but you get the point! Dashboards and models cannot guide any company's strategic actions directly. If companies try to use them without additional context, more often tha

What is SUTVA for A/B testing?

Imagine if person B’s blood pressure reading depends on whether person A receives the blood pressure medicine in a randomized controlled trial. This will be violating Stable Unit Treatment Value Assumption (SUTVA) SUTVA states that the treatment received by an individual should not influence the outcome we see for another individual during the experiment. I know the initial example sounded absurd, so let me try again. Consider LinkedIn A/B testing a new ‘dislike’ reaction for its users, and the gods of fate chose you to be part of the initial treatment group that received this update. Excited after seeing this new update, you use this dislike reaction on my post and send a screenshot to a few of your connections to do the same, who are coincidentally in the control group that did not receive the update. Your connections log in and engage with my posts to use this dislike reaction, but later get disappointed as this new update is not yet available to them. The offices of LinkedIn are tr

The Dumb Datum

Search This Blog