Calculating Percentages of Total Days with Four or More Published Videos in SQL

As a data analyst, it’s often necessary to calculate percentages of total days with four or more published videos. In this article, we’ll explore two solutions for Oracle and SQL Server, along with explanations and additional context to help you understand the concepts.

Understanding the Problem

Suppose we have a table with the following columns:

We want to calculate the percentage of days with four or more published videos. In other words, we need to count the number of days where there are four or more videos published and then divide that by the total number of days.

Solution for Oracle

One way to solve this problem in Oracle is using a combination of the DENSE_RANK function and the SUM aggregation function.

SELECT 
    SUM(perc) AS percent
FROM (
    SELECT 
        published_date,
        ROUND(
            100 * (COUNT(*) / 
                    SUM(COUNT(*)) OVER (), 2) AS perc,
            COUNT(published_date) CC
    FROM 
        dense_rank_demo
    GROUP BY 
        published_date
)
WHERE 
    CC >= 4;

Let’s break down how this query works:

• The subquery uses GROUP BY to group the data by published_date.
• For each group, it calculates the count of videos (COUNT(*)) and divides that by the total sum of counts across all groups using SUM(COUNT(*)) OVER (). This gives us a decimal value representing the percentage.
• The ROUND function rounds this value to two decimal places.
• The outer query sums up these percentages for all dates where there are four or more videos published (CC >= 4).

The comment on Stack Overflow explains that the last part of the calculation, 2), is from the ROUND function, which rounds the result to two decimal places.

Here’s an additional explanation using a sample dataset:

The DENSE_RANK function assigns a unique rank to each group based on the values. In this case, we have three groups:

• abc (9/1/2018): 3 videos
• dca (9/4/2018): 2 videos
• 5555 (9/1/2018): 3 videos

Now, let’s calculate the sum of counts across all groups using SUM(COUNT(*)) OVER (). This will give us:

The total sum of counts is 3 + 2 + 3 = 8.

Now, we divide the count for each group by this total sum:

• abc (9/1/2018): 3 / 8 ≈ 0.375
• dca (9/4/2018): 2 / 8 = 0.25
• 5555 (9/1/2018): 3 / 8 ≈ 0.375

The average percentage is (0.375 + 0.25 + 0.375) / 3 ≈ 0.333, which we round to two decimal places using the ROUND function.

Solution for SQL Server

For SQL Server, we can use a similar approach using window functions.

SELECT 
    SUM(perc) AS percent
FROM (
    SELECT 
        published_date,
        ROUND(
            (COUNT(published_date) * 100 / 
             (SELECT COUNT(*) FROM dense_rank_demo)) AS perc,
            COUNT(published_date) CC
    FROM 
        dense_rank_demo
    GROUP BY 
        published_date
)
WHERE 
    CC >= 4;

This query uses the SUM aggregation function to calculate the total sum of counts, and then divides that by the count of all records (COUNT(*)). This gives us the same average percentage as before.

However, we must note that SQL Server does not support DENSE_RANK directly. We use a subquery with COUNT(published_date) instead.

Additional Context

To better understand this problem and how to calculate percentages in SQL, let’s consider some additional examples:

• Suppose we have another table with more columns and we want to calculate the percentage of rows with four or more values for each column. How would you approach this?
• What if we want to exclude certain dates or rows from our calculation? How can we modify our query to achieve this?

Conclusion

Calculating percentages of total days with four or more published videos is a common problem in data analysis. Both Oracle and SQL Server have solutions using window functions, but the syntax and approach differ slightly between the two databases.

By understanding how these functions work and how to apply them correctly, you can calculate percentages for your own datasets and make informed decisions about your data.

Further Reading

• [Oracle Documentation: DENSE_RANK Function](https://docs.oracle.com/pls/dba/A Index/100204)
• SQL Server Documentation: SUM Window Function

In this article, we explored how to calculate percentages in SQL using window functions and explained the differences between Oracle and SQL Server approaches.

Last modified on 2023-11-06