Calculate performance based on rating table-CodePudding

I'm new to bigquery, I have a problem calculating correct rating for user because the rating definition is also depends on year and week.

Consider we have a table of users KPI

Year	Week	Task	User	KPI
2023	38	A	John	5.3
2023	39	A	Gia	4.8
2023	39	B	Gia	1.1
2023	40	A	Gia	5.0
2023	40	A	John	5.7

and performance table as reference for the rating.

Year	Week	Task	Base	Diff
2023	31	A	5	0.5
2023	31	B	1	0.1
2023	40	A	5.5	0.5

Case example, if a user doing task A in week 38, we need to evaluate the rating based on the definition of task A week 31. If the user has less than 5.0 KPI, it will have "top" rating, 5.0-5.5 for "average", and kpi above 5.5 for "low" rating.

I tried using a temporary function and nested select, but it returns correlated subquery error.

CodePudding user response：

Use below approach

WITH temp_kpi AS (
  SELECT *, PARSE_DATE('%Y %W', FORMAT('%i %i', Year, Week)) cut_date
  FROM user_kpi
), temp_performance AS (
  SELECT *, PARSE_DATE('%Y %W', FORMAT('%i %i', Year, Week)) cut_date
  FROM performance_lookup
)
SELECT k.* EXCEPT(cut_date),
  CASE 
    WHEN k.KPI < p.Base THEN 'top'
    WHEN k.KPI BETWEEN p.Base AND p.Base   p.Diff THEN 'average'
    WHEN k.KPI > p.Base   p.Diff THEN 'low'
    ELSE 'n/a'
  END AS rating
FROM temp_kpi k
LEFT JOIN (
  SELECT *, LEAD(cut_date, 1, CURRENT_DATE) 
    OVER(PARTITION BY Task ORDER BY cut_date) AS next_cut_date
  FROM temp_performance 
) p
ON k.Task = p.Task 
AND k.cut_date >= p.cut_date 
AND k.cut_date < p.next_cut_date

with result

CodePudding user response：

Would a simple join work?

select user_kpi.*,
       case when user_kpi.kpi < pl.base then 'top'
            when user_kpi.kpi between pl.base and pl.base   pl.diff
                 then 'average'
            when user_kpi.kpi > pl.base   pl.diff
                 then 'low'
        end as rating
  from user_kpi
 inner
  join performance_lookup pl
    on user_kpi.year = pl.year
   and user_kpi.week = pl.week
   and uesr_kpi.task = pl.task