Cumulative line chart

Learn how to build cumulative line charts to track growth and trends over time using SQL transformations for unique counts across months and countries.

A cumulative line chart is a type of chart that displays the cumulative sum of a variable over time. It is similar to a regular line chart, but instead of showing the value of the variable at each point in time, it shows the total value of the variable up to that point in time.

Cumulative line charts are often used to track changes in a variable over time and to identify trends and patterns in the data. They can be especially useful when working with data that accumulates or grows over time, such as sales figures or website traffic.

To create a cumulative line chart with a unique count, use the following SQL code to transform the data during the data preparation stage. The resulting column can be used for visualizing the chart on Vizpad.

SQL code to transform the data for creating a cumulative line chart

The output of the following SQL code can be used to create a cumulative line chart that shows trends in the number of unique campaigns over time and across different countries. The chart will show the total number of campaigns up to a certain point in time and allow for easy comparison of the total number of campaigns across different countries and over different time periods.

/* to display the cumulative number of unique campaigns per month and country */

select a.*, c.Cumulative_Campaigns from Table_Name a

 left join 

 (select b.*, SUM(b.Unique_Campaigns) over ( partition by b.Country order by b.Month ) as Cumulative_Campaigns from 

       ( select Month , Country , count(distinct Email_campaign_name ) as Unique_Campaigns 

 from Table_Name group by Month , Country ) b

 ) c

 on a.Month = c.Month and a.Country = c.Country

First subquery (b)

Initially, a subquery labeled (b) is created, which counts the number of unique campaigns per month and country. This is accomplished by selecting the Month, Country, and counting the distinct Email_campaign_name. Then, the subquery groups the data by Month and Country.

Second subquery with window function (c)

Next, a second subquery labeled as (c) uses the window function SUM() to calculate the cumulative number of unique campaigns per month and country (using the results from the first subquery (b)). The SUM() function is applied to the Unique_Campaigns column over a partition of Country, ordered by Month.

Outer query

The outer query selects all columns from the table (a) and the Cumulative_Campaigns column from the subquery (c) based on matching values for Month and Country. The left join ensures that all records from the table are included in the output, even if there are no matching records in the subquery.