My source table contains sales information. Each row is a person and records every time they've shopped/where. I can therefore calculate the average transaction value per industry by the following:
select
industry,
COALESCE(AVG(CASE WHEN shopcode in (1,2,4) THEN dollar END), 0) AS avt
from sales
group by industry
But source how can I adapt this to calculate the spend per distinct count of user i.e.: sum(dollar)/count(distinct person) so similar to above but instead of sum/count(*) sum/count(distinct person)... I need to use coalesce with this as well.