Hello,
SELECT
relation_id,
brand_id,
parent_absolute_num,
rel_absolute_num,
parent_find_num,
rel_find_num,
parent_prod_desc,
rel_prod_desc,
rank,
pct_transactions,
source_control_dt
FROM
(
SELECT
100 AS relation_id,
parent_absolute_num,
rel_absolute AS rel_absolute_num,
p1.base_find_num parent_find_num,
p2.base_find_num rel_find_num,
p1.friendly_desc AS parent_prod_desc,
p2.friendly_desc AS rel_prod_desc,
ROW_NUMBER() OVER(PARTITION BY
vb.brand_id,
main_absolute
ORDER BY
abs(nvl(p1.lowest_price,0) - nvl(p2.lowest_price,0) ),
rank
) AS rank,
pct_transactions * 100 AS pct_transactions,
vb.source_control_dt
FROM
vb vb,
prod p1,
prod p2
WHERE
vb.main_absolute = p1.absolute_num
AND
vb.brand_id = p1.brand_id
AND
vb.rel_absolute = p2.absolute_num
AND
vb.brand_id = p2.brand_id
AND
vb.pct_transactions >= 0.05
AND
p1.mdse_division = p2.mdse_division
AND
p1.mdse_category = p2.mdse_category
AND
p1.mdse_subcategory = p2.mdse_subcategory
AND
p1.prod_type = 0
AND
p2.prod_type = 0
AND NOT
EXISTS (
SELECT
1
FROM
lc.usr_suppress_also_bought u
WHERE
TRIM(u.parent_find_num) = p1.base_find_num
AND
TRIM(u.rel_find_num) = p2.base_find_num
AND
u.brand_id = vb.brand_id
)
AND
EXISTS (
SELECT
1
FROM
os.pdbw_brand_sku w
WHERE
w.sku = p1.sku
AND
w.brand_id = p1.brand_id
)
AND
EXISTS (
SELECT
1
FROM
os.pdbw_brand_sku w
WHERE
w.sku = p2.sku
AND
w.brand_id = p2.brand_id
)
)
WHERE
rank <= 20
/
Can You see any potential for tunning ? Like rewrite ?
Regards.
G