0

I have 2 tables, the first one is Orders as the following:

Customer_ID ORDER_ID STATUS
A 11 completed
A 12 completed
B 13 completed
B 14 completed
B 15 completed
C 16 completed
B 17 cancelled
A 18 cancelled

And the second one is Customers as the following:

Customer_ID Customer_status join_date
A 15 2022-02-09
b 15 2022-02-10
c 10 2022-02-10

I tried a query to use as a sub-query but it didn't work, I'm new to this and still struggling.

 SELECT T1.customer_id, count (T1.ORDER_ID) as Orders_count
 FROM orders T1 LEFT join customers T2
 on  T1.Customer_ID = T2.Customer_ID
 where T1.STATUS= 6 AND T2.Customer_status= 15
 AND T2.join_date between timestamp'2022-02-10 'and timestamp '2022-02-11'
 GROUP  BY T1.Customer_ID ORDER  BY T1.Customer_ID

I want to categorize the users as eligible or ineligible for a bonus. The eligibles are the ones whose user_status = 15, who made more than 1 order, and whose joining date is 2022-02-10, others are ineligible. I want the table to show both, I'm using redash for that matter.

Nancy Elhossiny
  • 1
  • 1

1 Answers1

0

group by and having can be used to create a list of customers who have placed many orders.

Then a left join to join this list and see who is eligible or not.

select c.*, if(s.Customer_ID is not null, 'YES','NO') as Eligible
from customers c 
left join (
  select Customer_ID
  from orders
  group by Customer_ID
  having count(1) > 1
) as s on s.Customer_ID = c.Customer_ID and c.Customer_status = 15 and c.join_date = '2022-02-10'

Demo here

SelVazi
  • 10,028
  • 2
  • 13
  • 29
  • i don't believe, that he has the right rdms tagged, besides without the status, how can you tell which row he actually wants – nbk Feb 19 '23 at 21:37
  • According to the description, the eligible ones are the customers with customer_status 15 and join date 2022-02-10 and having more than one order. – SelVazi Feb 19 '23 at 21:53
  • the order status is the relevant status to get the right row, but he hasn't added the data, so your query is only a gues – nbk Feb 19 '23 at 21:57
  • the eligible ones are the customers with customer_status 15 and join date 2022-02-10 and having more than one completed order. and I need the count in a row – Nancy Elhossiny Feb 20 '23 at 12:46
  • count of what ? – SelVazi Feb 20 '23 at 13:17