SQL query to sort list of clients

Question

I need to write a SQL query that will return a list of customers who were created in 2020 and who have at least 1 paid order. The final table should contain the following data:

client id

email

date of creation of the first paid order

category of the first paid order

[Client]

[Order]

question from:https://stackoverflow.com/questions/65924187/sql-query-to-sort-list-of-clients

Answer 1

here is how you can do it :

select u.id , u.email , oo.category,oo.created_at 
from users u
cross apply ( select TOP 1 * 
       from orders 
       where o.user_id = u.id 
       order by created_at asc
      ) oo
where u.created between '1-1-2020' and '30-12-2020'