Skip to content

Instantly share code, notes, and snippets.

@jaymay
Created May 5, 2015 15:50
Show Gist options
  • Save jaymay/1fc0856e6ee810880fc0 to your computer and use it in GitHub Desktop.
Save jaymay/1fc0856e6ee810880fc0 to your computer and use it in GitHub Desktop.
select i.full_name industry, widget_accounts, all_customers, round((widget_accounts / all_customers) * 100, 2) perc_accounts_w_widget
from
(
select industry, count(*) all_customers, sum(if(w.widget_ticket_last_7 > 0, 1, 0)) widget_accounts
from accounts a
left outer join
(select t.account_id, max(run_at) max_run_at, sum(t.new_channel_zendesk_widget) widget_ticket_last_7
from tickets t
where run_at between subdate(date(now()), interval 7 day) and date(now())
and t.new_channel_zendesk_widget > 0
group by account_id
) w on a.account_id = w.account_id and a.run_at = w.max_run_at
where a.run_at = (select max(run_at) from accounts)
and a.timespan = 'all'
and a.account_type = 'customer'
group by industry
) results
inner join industry i on i.name = results.industry
order by perc_accounts_w_widget desc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment