median invite interval 2016.04.20

SQL

# finding avg
#select user_id, invite_date, bucket from u2041__thr_p.th2_experimental_user limit 10;
/*
select AVG(invite_interval) as average_invite_interval FROM
(
SELECT user_id, 
	TIMESTAMPDIFF(
		HOUR, 
		DATE_FORMAT(user_registration,"%Y%m%d%H%i%S"), 
		DATE_FORMAT(invite_date, "%Y%m%d%H%i%S")
	) AS invite_interval,
  bucket FROM
  u2041__thr_p.th2_experimental_user AS th2
  LEFT JOIN enwiki_p.user AS u
  USING (user_id)
  ) AS tmp;
*/

# finding mode
/*SELECT invite_interval, COUNT(*) AS most_common_invite_interval FROM
(
SELECT user_id, 
	TIMESTAMPDIFF(
		HOUR, 
		DATE_FORMAT(user_registration,"%Y%m%d%H%i%S"), 
		DATE_FORMAT(invite_date, "%Y%m%d%H%i%S")
	) AS invite_interval,
  bucket FROM
  u2041__thr_p.th2_experimental_user AS th2
  LEFT JOIN enwiki_p.user AS u
  USING (user_id)
  ) AS tmp
GROUP BY invite_interval
ORDER BY most_common_invite_interval DESC
LIMIT 1;
*/

# finding median
# pull table as before, sort by invite interval. then add a row id count 1-#. then, determine middle or middle two numbers, pull first twice or both and div by 2.

SELECT user_id, 
	TIMESTAMPDIFF(
		HOUR, 
		DATE_FORMAT(user_registration,"%Y%m%d%H%i%S"), 
		DATE_FORMAT(invite_date, "%Y%m%d%H%i%S")
	) AS invite_interval,
  bucket FROM
  u2041__thr_p.th2_experimental_user AS th2
  LEFT JOIN enwiki_p.user AS u
  USING (user_id)
    ORDER BY invite_interval ASC;
SQL

Checking query status...
