1SELECT 
2  work_year,
3  -- Average salary for Medium companies
4  ROUND(
5    SUM(CASE WHEN company_size = 'M' THEN salary_in_usd END) / 
6    SUM(CASE WHEN company_size = 'M' THEN 1 END), 
7    2
8  )/1000 AS avg_salary_M_kdollar,
9  -- Average remote ratio for Medium companies
10  ROUND(
11    AVG(CASE WHEN company_size = 'M' THEN remote_ratio END), 
12    2
13  ) AS avg_remote_ratio_M,
14  -- Average salary for Large companies
15  ROUND(
16    SUM(CASE WHEN company_size = 'L' THEN salary_in_usd END) / 
17    SUM(CASE WHEN company_size = 'L' THEN 1 END), 
18    2
19  )/1000 AS avg_salary_L_kdollar,
20  -- Average remote ratio for Large companies
21  ROUND(
22    AVG(CASE WHEN company_size = 'L' THEN remote_ratio END), 
23    2
24  ) AS avg_remote_ratio_L,
25  -- Average salary for Small companies
26  ROUND(
27    SUM(CASE WHEN company_size = 'S' THEN salary_in_usd END) / 
28    SUM(CASE WHEN company_size = 'S' THEN 1 END), 
29    2
30  )/1000 AS avg_salary_S_kdollar,
31  -- Average remote ratio for Small companies
32  ROUND(
33    AVG(CASE WHEN company_size = 'S' THEN remote_ratio END), 
34    2
35  ) AS avg_remote_ratio_S
36FROM 
37  @kaggle.saurabhbadole_latest_data_science_job_salaries_2024.datascience_salaries_2024
38GROUP BY 
39  work_year
40ORDER BY 
41  work_year;