Forem: Sumeet Bansal

Calculate average delta change in KPIs over a period of time.

Sumeet Bansal — Sun, 21 Aug 2022 20:22:21 +0000

It is often that we have to calculate delta change in KPIs over a period of time. It might be on a daily, weekly, monthly or quarterly level. Monitoring changes in a product's KPIs is a very crucial task. Analysing trends will help us improve products.

In this blog, we will discuss a similar situation where we have to find the average monthly increase/decrease in total visits for an article in the last 6 months.

(Table contains data for the last 7 months for each article)

╔════════════════╦═══════════════╦═════════════════╗
║  article_id    ║ year_month    ║ total_visits    ║
╠════════════════╬═══════════════╬═════════════════╣
║ 1              ║ 202201        ║ 700             ║
╠════════════════╬═══════════════╬═════════════════╣
║ 1              ║ 202202        ║ 1100            ║
╠════════════════╬═══════════════╬═════════════════╣
║ 1              ║ 202203        ║ 1200            ║
╠════════════════╬═══════════════╬═════════════════╣
║ 1              ║ 202204        ║ 1440            ║
╠════════════════╬═══════════════╬═════════════════╣
║ 1              ║ 202205        ║ 4300            ║
╠════════════════╬═══════════════╬═════════════════╣
║ 1              ║ 202206        ║ 4500            ║
╠════════════════╬═══════════════╬═════════════════╣
║ 1              ║ 202207        ║ 4100            ║
╠════════════════╬═══════════════╬═════════════════╣
║ 5              ║ 202203        ║ 9800            ║
╠════════════════╬═══════════════╬═════════════════╣
║ 5              ║ 202204        ║ 12000           ║
╠════════════════╬═══════════════╬═════════════════╣
║ 5              ║ 202205        ║ 14000           ║
╚════════════════╩═══════════════╩═════════════════╝
--------------------------------------------------------------
The expected output for the article with ID 1
╔════════════════╦═════════════╗
║  article_id    ║ monthly_avg ║
╠════════════════╬═════════════╣
║ 1              ║ 760         ║
╚════════════════╩═════════════╝

The first step of solving this would be calculating the difference in visits between the current month and last month for every article. Here we can use a special function called LAG.

LAG function provides access to a row at a given physical offset that comes before the current row, which means the function gives us the nth last row value of a particular column. It takes at least 2 arguments in this case, first is the column and second is the offset.

We create a CTE to calculate the difference in visits by subtracting the current month's visits from last month's visits which we are getting by using the LAG function.

WITH cte_articles
AS (
    SELECT article_id
        ,year_month
        ,total_visits - (
            LAG(total_visits, 1) OVER (
                PARTITION BY article_id ORDER BY year_month ASC
                )
            ) AS diff_in_visits
    FROM articles
    )

The difficult task is now done. You must be wondering why didn't we add the filter to include just the last 6 months of data. That is because for calculating the monthly difference of the last 6 months we need 7 months of data and when the LAG function gives us the last offset value of row for the first month we get null.

This null is ignored when we calculate the average difference in visits using the AVERAGE function.

Calculating the average for every article.

SELECT article_id
    ,avg(diff_in_visits) AS monthly_avg
FROM cte_articles
GROUP BY article_id;

This is how easily we can calculate average delta changes in KPIs using just SQL.

Getting started with reddit.

Sumeet Bansal — Mon, 15 Aug 2022 14:46:00 +0000

Reddit is a popular social news website that has been gaining more and more viewers over the years. With such a diverse range of topics covered on its pages, there is bound to be something for everyone. In this blog, we'll go over what you need to know about reddit and how it can help you.

How to use reddit?

If you're new to reddit, the front page can be overwhelming. There are thousands of subreddits (sections of reddit), and each one is full of posts. How do you find the good stuff?

What are subreddits?

A subreddit is a specific online community, with its own set of rules and regulations, that is dedicated to a particular topic. For example, the reddit community r/AskReddit is a subreddit dedicated to asking and answering questions.

Subreddits are created and moderated by volunteers who are passionate about their topic. Anyone can create a subreddit, and anyone can subscribe to any number of subreddits. When you subscribe to a subreddit, the content from that subreddit will appear in your feed.

Finding subreddits

Check out the default subreddits. Default subreddits are chosen by the reddit team and they cover a range of topics. They're a great starting point for finding new content.
Use the search function. On the top-right of the screen, you'll see a search bar. You can use this to find specific subreddits or topics that you're interested in.
Browse by topic. If you click on the "Browse" link at the top of the screen, you'll see a list of popular categories. This is a great way to explore different areas of reddit.
If you're new to reddit, or if you're just looking to find some new communities to join, browsing through the most popular subreddits is a great way to get started.

Why should you use Reddit for help with programming?

If you're a programmer who likes to learn new stuff or is an experienced one, you should definitely check out Reddit. Why? Because Reddit is full of people who are passionate about sharing knowledge related to programming.

You can find virtually anything on Reddit, from useful tips and tricks to in-depth discussions about your programming language. And since Reddit is organized into communities (or "subreddits"), it's easy to find the information you're looking for.

Not only that, but Reddit is also a great place to get feedback on your work. If you have built projects and want to improve them based on community reviews reddit is the best place as people will get to know about your project and will provide suggestions to improve it.

What are some tips for using reddit more effectively?

First, take some time to explore the different subreddits and find ones that fit your interests. There's a subreddit for just about everything, so you're bound to find some that you enjoy.
Once you've found some good subreddits, make sure to upvote the content that you like and downvote the content that you don't. This helps ensure that the best content rises to the top.
Feel free to add your own comments and contribute to the discussions taking place. Reddit is a great community and its users are always happy to help out newcomers.
Finally, if you really want to get involved in the reddit community, consider creating your own subreddit. This is a great way to bring people together who share your interests.

Dark side of reddit

Like every other social media reddit is also prone to toxicity. You might get downvoted for no reason. You might get abused for going against popular opinion. You might also get banned for subreddits for breaking rules that you were never aware of. You will also have to aware of scammers and other stuffs that are common across all the other social media sites.

Some of my favourite programming subbreddits

r/programming
r/LearnProgramming
r/cscareerquestions
r/AskProgramming
r/BadCode
r/ProgrammerHumor
r/CodingHelp

Do let me know of your favourite subreddits!!

Finding mean, median, and mode using SQL.

Sumeet Bansal — Wed, 10 Aug 2022 22:10:10 +0000

It is very often that we need to calculate averages in our data for various analysis tasks. It quite easy when it come to calculating Mean, Median and Mode in tools such as Excel with the help of inbuilt functions.

In this this blog we will discuss ways to calculate these values using just SQL.

Let us consider table below to find mean, median, and mode of salaries department wise for all the departments.

╔═══════╦════════╦════════╗
║ empid ║ deptid ║ salary ║
╠═══════╬════════╬════════╣
║ 1     ║ 1      ║ 12000  ║
╠═══════╬════════╬════════╣
║ 2     ║ 2      ║ 3400   ║
╠═══════╬════════╬════════╣
║ 3     ║ 3      ║ 45000  ║
╠═══════╬════════╬════════╣
║ 4     ║ 4      ║ 1320   ║
╠═══════╬════════╬════════╣
║ 5     ║ 4      ║ 5600   ║
╠═══════╬════════╬════════╣
║ 6     ║ 2      ║ 2700   ║
╠═══════╬════════╬════════╣
║ 7     ║ 1      ║ 12000  ║
╠═══════╬════════╬════════╣
║ 8     ║ 3      ║ 34000  ║
╠═══════╬════════╬════════╣
║ 9     ║ 4      ║ 4600   ║
╠═══════╬════════╬════════╣
║ 10    ║ 1      ║ 9000   ║
╠═══════╬════════╬════════╣
║ 11    ║ 1      ║ 13000  ║
╠═══════╬════════╬════════╣
║ 12    ║ 2      ║ 12000  ║
╠═══════╬════════╬════════╣
║ 13    ║ 4      ║ 1320   ║
╠═══════╬════════╬════════╣
║ 14    ║ 3      ║ 24000  ║
╠═══════╬════════╬════════╣
║ 15    ║ 3      ║ 45000  ║
╠═══════╬════════╬════════╣
║ 16    ║ 2      ║ 3400   ║
╠═══════╬════════╬════════╣
║ 17    ║ 2      ║ 1230   ║
╠═══════╬════════╬════════╣
║ 18    ║ 1      ║ 15000  ║
╚═══════╩════════╩════════╝

Finding mean is quite easy as it is just the average of all the salary for a department and we can use the AVG function for that.

SELECT deptid
    ,AVG(SALARY) AS mean_salary
FROM employee
GROUP BY deptid

Calculating mode of salary for each department will be a bit tricky as we don't have inbuilt SQL functions for that.

Steps for it are :

For this we start by calculating salary count for each unique salary in each department.
Then we rank the salary by its frequency (salary count) in each department.

salary_counts AS (
    SELECT deptid,
        salary,
        COUNT(salary) AS salary_counts
    FROM employee 
    GROUP BY deptid, salary 
)
-- ranking salaries by frequency
, mode_salaries AS (
    SELECT deptid,
        salary,
        RANK() OVER( PARTITION BY deptid ORDER BY salary_counts DESC ) AS salary_counts_rank
    FROM salary_counts
)
SELECT deptid,
    salary as mode_salary
FROM mode_salaries
WHERE salary_counts_rank = 1

For calculating median of salary for each department PERCENTILE_DISC SQL function along with GROUP can help us calculate that.

median_salaries AS (
    SELECT deptid,
        PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY salary) AS median_salary
    FROM employee
    GROUP BY deptid
)
SELECT deptid,
    median_salary 
FROM median_salaries

And that is how we can calculate mean, median, mode in SQL.