Avoid using CROSS JOIN on my SQL query (too heavy)

Question

I am working on an SQL query in order to define customer types, the goal is to differenciate the old active customers from the churn customers (churn = customers that stopped using your company's product or service during a certain time frame)

In order to do that, i came up with this query that works perfectly :

WITH customers AS (
  SELECT 
    DATE(ord.delivery_date) AS date,
    ord.customer_id 
  FROM table_template AS ord
  WHERE cancel_date IS NULL 
    AND order_type_id IN (1,3) 
  GROUP BY DATE(ord.delivery_date), ord.customer_id, ord.delivery_date),

days AS (SELECT DISTINCT date FROM customers),

recap AS (
    SELECT * FROM (
        SELECT  
            a1.date,
            a2.customer_id,
            MAX(a2.date) AS last_order,
            DATE_DIFF(a1.date, MAX(a2.date), day) AS days_since_last,
            MIN(a2.date) AS first_order,
            DATE_DIFF(a1.date, MIN(a2.date), day) AS days_since_first
        FROM days AS a1
        CROSS JOIN customers AS a2 WHERE a2.date <= a1.date
        GROUP BY a1.date, customer_id)
  )

SELECT * FROM recap 

The result of the query :

The only issue of this query is that the calculation is too heavy (it uses a lot of CPU seconds) I think that it is due to the CROSS JOIN.

I need some of your help in order to find another way to come with the same result, a way that doesn't need a CROSS JOIN to have the same output, do you guys think it is possible ?

Answer 1

As you have mentioned the problem of query taking a long time to load was because of the internet issue. Also, I will try to explain Inner Join further with a sample query as below:

SELECT distinct a1.id,a1.date    
FROM `table1` AS a1
INNER JOIN `table2` AS a2
ON a2.date <= a1.date

The INNER JOIN selects all rows from both the tables as long as the condition satisfies. In this sample query it gives the result based on condition a2.date <= a1.date only if date values in table1 are greater than or equal to date values in table2.

Input Table 1:

Input Table 2:

Output Table: