Everyday SQL (13) interview question

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.