We have 3 tables
Orders table (ORDER_ID, CUSTOMER_ID, ORDER_DATE)
Detail table (ORDER_ID, PRODUCT_ID, PRICE, UNITS)
Customers table (CUSTOMER_ID, SIGNUP_DATE, COUNTRY)
Data request: can you write a query to calculate % of customers that made an order each month, by signup month.
Please return 3 columns: signup month, order month, % of customers
Possible solution:
with table1 as
( select extract (year from signup_date) as year, extract (month from signup_date) as month, extract(month from order_date) as order_month,count(distinct customer_id) as counted from customer_table C left join Orders_table O on C.customer _id=O.customer_id),
table2 as
( select extract (year from signup_date) as year, extract (month from signup_date) as month, extract(month from order_date) as order_month, count(distinct customer_id) as counted from customer table left join order table on customer_id=customer_id where order_id is not null group by 1,2,3)
select table1.year,table1.month, table1.order_month, table1.counted/table2.counted as % from table1 left join table2 on table1.year=table2.year and table2.month=table2.month and table1.order_month=table2.order_month
Another understanding of this question would be a customer who has an order each month after the customer signed up. It would be a different story. If you have time, take a look at it. It’s interesting.