-2

I'm having trouble joining 2 tables that will allow me to see which new customers purchased at least 1 of a specific item. The first one pulls new customer accounts from a specific date range, and the second pulls customers that made at least on purchase of an item during a specific date range

select customer.id from customer c
inner join device d on u.id = d.user_id
where c.created_date between {{start}} and {{end}}
group by c.id

and

select customer_id from purchase_history 
where item_id = {{itemID}} and created_date between {{starts}} and {{ends}} group by customer_id

I'm stuck with the join part, which always confuses me but here is what I have right now:

select customer.id from customer c 
INNER JOIN device d on u.id = d.user_id 
INNER JOIN
     (select customer_id from purchase_history 
where item_id = {{itemID}}
     ) c
     on c.customer = customer_id.purchase_history
where created_date between {{starts}} and {{ends}} 
group by customer_id

Thank you!!

The main goal is to provide a date range, and an item ID that will then pull the list of new customers (first code) that purchased that certain item (second code) over a certain date range. The three tables (customer, device, and purchase history) all have the customer ID column in them.

bababababa
  • 43
  • 5

1 Answers1

0

How about this:

select id
from customer c
  INNER JOIN device d on d.user_id = c.id
  INNER JOIN purchase_history ph on c.id = ph.customer_id
where ph.item_id = {{itemID}}
  and ph.created_date between {{starts}} and {{ends}} 
group by ph.customer_id

If that doesn't help, maybe you could provide an example of each table you are joining and what you would like out. From what you posted, it looks like all you want is the customer ID.

Doyle B
  • 104
  • 6
  • Yes! So I want to pull the customer ID (on customer table) after giving a date range and an item id to see which of the new customers (on device table) made a purchase (on purchase history). Right now, it keeps on giving me an error with the matching of the ids. – bababababa Feb 22 '21 at 22:01
  • With the understanding that the created_date column is in the purchase_history table, I've edited the above query. If that doesn't work, can you paste the full error you are getting? – Doyle B Feb 22 '21 at 23:27
  • If that worked for you, do you mind selecting my answer as the correct answer? Thx! – Doyle B Feb 23 '21 at 00:01
  • Just to observe, there is no problem in sql for which the above would be a valid solution – Strawberry Feb 23 '21 at 07:53