How to display total number of records that occured each week and met some conditions SQL

Question

Could you please help me to write a SQL query that will return information about the number of how many upgrades has occurred in each weeks and the change vs prior week. Final result should show only data for the existing users.

table example:

PROJECT ID     EVENT DATE    EVENT TYPE    USER_TYPE
1              01012020      upgrade       existing
2              02012020      downgrade     new
3              04012020      upgrade       existing
1              05012020      upgrade       existing
2              07012020      downgrade     new
3              08012020      upgrade       existing
1              09012020      downgrade     existing
2              1012020       upgrade       new

What I wanted to do:

SELECT
  DATEPART(week, event_date) As Week,
  COUNT(PROJECT_IS) 
FROM Customers
GROUP BY DATEPART(week, event_date)
ORDER BY DATEPART(week, event_date);

But i don't know how to include if statement here + that I should show results only for existing users. I am quite fresh when it comes to SQL so any suggestions are welcome. Thanks

EXPECTED RESULTS:

USER_TYPE   EVENT_TYPE   NUMBER_OF_UPGRADES          WEEK     DIF_WEEK
existing    upgrade      (total number of upgrades)  Week 1   Week1
existing    upgrade      (total number of upgrades)  Week 2   Week2-Week1

Answer 1

For existing users, number of upgrade per week can be achieved using the following query:

SELECT 'existing' AS USERTYPE, 
       'upgraded' AS EVENT_TYPE,
       COUNT(1),
       DATEPART(week, event_date) As Week
  FROM Customers 
 WHERE user = 'existing' and event_type = 'upgraded' -- conditions here
GROUP BY DATEPART(week, event_date) 
ORDER BY DATEPART(week, event_date);

Answer 2

Assuming you are using SQL Server, then you want something like this:

SELECT user, event_type,
       YEAR(event_date) as year, DATEPART(week, event_date) As Week
       COUNT(*) as this_week
       COUNT(*) - LAG(COUNT(*)) OVER (PARTITION BY user, event_type ORDER BY MIN(event_date)) as diff
FROM Customers 
WHERE user = 'existing' and event_type = 'upgraded' -- conditions here
GROUP BY user, event_type, YEAR(event_date), DATEPART(week, event_date) 
ORDER BY DATEPART(week, event_date);

Notes:

• Be very careful with dates. The week without the year can be quite dangerous, so I included that in both the SELECT and GROUP BY.
• I included the user/event_type in both the SELECT and GROUP BY. This makes it easy to show all groups or to change the groups.
• The LAG() calculates the difference between two rows.