I want to calculate customer retention week over week. My sales_orders table has columns order_date and customer_name. Basically I want to check if a customer in this week also had an order the previous week. To do this, I have used CASE WHEN and subquery as follows (I have extracted order_week in a CTE I've called weekly_customers and gotten distinct customer names within each week):

SELECT wc.order_week, wc.customer,
    CASE
        WHEN wc.customer IN (
            SELECT sq.customer FROM weekly_customers sq WHERE
               sq.order_week = (wc.order_week - 1)) 
        THEN 'YES'
        ELSE 'NO'
    END  AS present_in_previous_week
FROM weekly_customers AS wc

The query returns the correct data. My issue is that the table is really huge with about 15000 distinct weekly values. This obviously leads to very long execution time. Is there a way I can improve this loop or even an alternative to the loop altogether?