how to comparing dataset to new data coming every week using SQL in Databricks

Question

I want to find the number of ids changing their value every week and month

every week a new dataset get entered into the database, every week same ids are added with their values, some weeks values for some ids change.

I want to find the amount of ids change per month and week for all the data I have over the last 2 years.

all of this is being done in databricks

I have attached an example data-set, where data is entered for 3 ids for two months and 2 ids changed their value. the desired output shows what I need is that the second month showing 2 changes and the first showing 0 changes.

dataset

output needed

Join to adjacent month and different values. Simple group by at that point. — shawnt00, Aug 10 '21 at 18:18
@shawnt00 you mean inner join? how would you do it to adjacent month? is there a funtion — H.M, Aug 10 '21 at 18:29
https://docs.databricks.com/sql/language-manual/functions/add_months.html — shawnt00, Aug 10 '21 at 19:33

score 0 · Answer 1 · answered Aug 20 '21 at 11:51

Use JOIN clause to adjacent month and different values and apply GROUP BY clause. On the resulting dataset apply the add_months function.

add_months(startDate, numMonths)

The startDate will take your Date and numMonth will take the values from the resulting GROUP BY clause.

how to comparing dataset to new data coming every week using SQL in Databricks

1 Answers1