Screen Link:
WITH
payment_with_year_month_table AS (
SELECT *,
CAST(SUBSTR(paymentDate, 1,4) AS INTEGER)*100 + CAST(SUBSTR(paymentDate, 6,7) AS INTEGER) AS year_month
FROM payments p
),
customers_by_month_table AS (
SELECT p1.year_month, COUNT(*) AS number_of_customers, SUM(p1.amount) AS total
FROM payment_with_year_month_table p1
GROUP BY p1.year_month
),
new_customers_by_month_table AS (
SELECT p1.year_month,
COUNT(*) AS number_of_new_customers,
SUM(p1.amount) AS new_customer_total,
(SELECT number_of_customers
FROM customers_by_month_table c
WHERE c.year_month = p1.year_month) AS number_of_customers,
(SELECT total
FROM customers_by_month_table c
WHERE c.year_month = p1.year_month) AS total
FROM payment_with_year_month_table p1
WHERE p1.customerNumber NOT IN (SELECT customerNumber
FROM payment_with_year_month_table p2
WHERE p2.year_month < p1.year_month)
GROUP BY p1.year_month
)
SELECT year_month,
ROUND(number_of_new_customers*100/number_of_customers,1) AS number_of_new_customers_props,
ROUND(new_customer_total*100/total,1) AS new_customers_total_props
FROM new_customers_by_month_table;
i am just very confused on this…
WHERE p1.customerNumber NOT IN (SELECT customerNumber
FROM payment_with_year_month_table p2
WHERE p2.year_month < p1.year_month)
what is this doing exactly? someone else asked this through another topic but there is no reply…