select
	date(date) as date,
	coalesce(sum(case when event_steps = 'tutorial' then event_count end), 0)
		as "tutorial",
	coalesce(sum(case when event_steps regexp '^(tutorial,)?file$' then event_count end), 0)
		as "file",
	coalesce(sum(case when event_steps regexp '^(tutorial,)?file,deeds$' then event_count end), 0)
		as "deeds",
	coalesce(sum(case when event_steps regexp '^(tutorial,)?file,deeds,details$' then event_count end), 0)
		as "details",
	coalesce(sum(case when event_steps regexp '^(tutorial,)?file,deeds,details,thanks.*$' then event_count end), 0)
		as "thanks",
	coalesce(sum(
		case
			when event_steps != 'tutorial'
			and event_steps not regexp '^(tutorial,)?file$'
			and event_steps not regexp '^(tutorial,)?file,deeds$'
			and event_steps not regexp '^(tutorial,)?file,deeds,details$'
			and event_steps not regexp '^(tutorial,)?file,deeds,details,thanks.*$'
			then event_count
		end
	), 0)
		as "broken"
from (
	select
		left(timestamp, 8) as date,
		cast(event_steps as char) as event_steps,
		count(event_flowId) as event_count
	from (
		select
			event_flowId,
			min(timestamp) as timestamp,
			group_concat(event_step order by
				timestamp,
				case
					when event_step = 'tutorial' then 1
					when event_step = 'file' then 2
					when event_step = 'deeds' then 3
					when event_step = 'details' then 4
					when event_step = 'thanks' then 5
				end
			) as event_steps
		from UploadWizardStep_8612364
		where timestamp < ( select min(timestamp) from UploadWizardStep_8851805 )
		group by left(timestamp, 8), clientIp, userAgent
		union all
		select
			event_flowId,
			min(timestamp) as timestamp,
			group_concat(event_step order by
				timestamp,
				case
					when event_step = 'tutorial' then 1
					when event_step = 'file' then 2
					when event_step = 'deeds' then 3
					when event_step = 'details' then 4
					when event_step = 'thanks' then 5
				end
			) as event_steps
		from UploadWizardStep_8851805
		where timestamp < ( select min(timestamp) from UploadWizardStep_11772724 )
		group by event_flowId, clientIp, userAgent
		union all
		select
			event_flowId,
			min(timestamp) as timestamp,
			group_concat(event_step order by event_flowPosition) as event_steps
		from UploadWizardStep_11772724
		group by event_flowId
	) t
	group by left(timestamp, 8), event_steps
	order by event_count desc, length(event_steps) asc
) u
group by date
