[SQL] How to Show Previous Value of Previous Date Metric based on Multiple Dimensions in...

I have a dataset consist a date, name, country and group dimensions and several metrics as below.

Example of dataset

I want create another table that consist another dimension for previous_date based on the current data in the same table. Then, i need to add another metric i.e. previous_metric that would display the value of that same metric during the same selected date period but for the next month date.

Here is the sample of output that I'm desired.



Here is what I have done so far.

WITH first_table AS (
SELECT current_date, DATE(DATETIME_SUB(current_date, INTERVAL 1 YEAR)) AS previous_date,
name, Country, Group, metric, percent
FROM `...mytable`),

second_table AS (
SELECT current_date, name, Country, Group, metric AS previous_metric, percent
FROM `...mytable`)

SELECT a.current_date, a.previous_date, a.name, a.Country, a.Group, a.salesperson,
a.metric, b.previous_metric, a.percent
FROM first_table AS a FULL JOIN second_table AS b
ON b.current_date = a.previous_date
AND b.name = a.name
AND b.Country = a.Country
AND b.Group = a.Group


Because this table has quite numbers of dimension, it's bit difficult to just joint based on the current date only. It also need to consider other dimension i.e. name, country and group.

However, the result seems to have double amount from what it should be. May I know what's wrong with my code? Is there any other way to solve and create this table?

Continue reading...