So I have a simple table that holds items we offered to a customer and the items the user actually used, per day.
date | offered_name | used_name | h_id
----------------------------------------------------------
2019-06-20 | Obsidian | Obsidian | 100
2019-06-20 | Obsidian | Limestone | 101
2019-06-20 | Limestone | Sandstone | 102
2019-06-21 | Obsidian | Limestone | 100
2019-06-21 | Obsidian | Sandtone | 101
2019-06-21 | Limestone | Limestone | 102
I want to find all the instances where the offered item matches the used item. The user can change their used_item, so I only care if they have ever matched the offered_name at least once. If they have never matched then I don’t want to select them. Output for the above would look like:
h_id | used_offered_item_at_least_once
---------------------------------------
100 | 1
101 | 0
102 | 1
Similar to this question SQL - find all instances where two columns are the same but I want to compare two different columns rather than checking just the one.
You can use conditional aggregation
select h_id,
cast(sign(sum(case when offered_name = used_name then
1
else
0
end)) as int) as used_offered_item_at_least_once
from tab
group by h_id