Window Function causing Processing Error

VictorEjiasi

197,559,985 rows

ArborRose

The Beast Mode calculation appears to be causing a processing error, likely due to the combination of a window function and the dataset size. The calculation for repeat customer revenue may be leading to the error. To avoid the complexity of window functions and reduce processing load, the OVER (PARTITION BY), appears to be too resource intensive for the large dataset.

Maybe simplify customer repeat count - instead of using a window function to calculate repeat customers, try identifying repeat customers by counting purchases using a conditional function.

COUNT(DISTINCT CASE WHEN `Customer_ID` IS NOT NULL THEN `Customer_ID` END)

And adjust the revenue calculation for repeat customers.

CASE WHEN COUNT(`Order_ID`) OVER (PARTITION BY `Customer_ID`) > 1 THEN 1 ELSE 0 END

This should sum up the revenue from customers who have made more than one purchase. If this doesn't work, you might try aggregating the repeat customer revenue in a Magic ETL. That would simplify the logic needed and reduce the amount of data for the card to process.

ArborRose

The Beast Mode calculation appears to be causing a processing error, likely due to the combination of a window function and the dataset size. The calculation for repeat customer revenue may be leading to the error. To avoid the complexity of window functions and reduce processing load, the OVER (PARTITION BY), appears to be too resource intensive for the large dataset.

Maybe simplify customer repeat count - instead of using a window function to calculate repeat customers, try identifying repeat customers by counting purchases using a conditional function.

COUNT(DISTINCT CASE WHEN `Customer_ID` IS NOT NULL THEN `Customer_ID` END)

And adjust the revenue calculation for repeat customers.

CASE WHEN COUNT(`Order_ID`) OVER (PARTITION BY `Customer_ID`) > 1 THEN 1 ELSE 0 END

This should sum up the revenue from customers who have made more than one purchase. If this doesn't work, you might try aggregating the repeat customer revenue in a Magic ETL. That would simplify the logic needed and reduce the amount of data for the card to process.

VictorEjiasi

Thanks