0

I have the following script:

SELECT * 
FROM (
          SELECT distinct web_product_source_id, recipe_id,  cos_score,
                row_number() over (partition by web_product_source_id order by cos_score desc) as rnk 
          FROM (
                    SELECT  web_product_source_id, recipe_id, max(cos_score) cos_score
                    FROM edw_sandbox.combined_with_product_details_ouput_cleaned
                    WHERE web_supercat_source_id = 'cookware'
                    GROUP BY 1,2
                ) a
    ) b
WHERE rnk <= 20
ORDER BY web_product_source_id, rnk;

I want to add an if else statement in the "WHERE" statement. I want the data to pull top 20 ranks or the top 40 if web_product_source_id id contains any of the following terms ('cookware', 'set', etc...)

Sudo Code:

if 'set' or 'cookware' in web_product_source_id id: pull top 40 ranks
else: pull top 20 ranks and cos_score >= .7
Maria Nazari
  • 660
  • 1
  • 9
  • 27
  • 2
    Perhaps something like: `WHERE rnk <= CASE WHEN web_product_source_id IN ('set','cookware') THEN 40 ELSE 20 END AND cos_score >= CASE WEN web_product_source_id NOT IN ('set', 'cookware') THEN .7 ELSE -999999999 END` – JNevill Sep 04 '18 at 19:21
  • @JNevill thank you! exactly what I needed – Maria Nazari Sep 04 '18 at 20:23

0 Answers0