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 AVG(invite_interval) AS invite_interval_median  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)
  ORDER BY invite_interval ASC
  ) AS tmp
  */
   /*
(
	SELECT @counter:=@counter+1 AS row_id, ??? 
	from tmp?, (select @counter:=0)
	order by invite_interval??????
) part1
join
(
	select count(*) as total_rows
    from tmp
) part2
where part1.row_id in(???) (floor((part2.total_rows + 1)/2), floor((part2.total_rows + 2)/2))
  
  )*/

#version 2 median
/*
SELECT AVG(invite_interval) AS invite_interval_median  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)
  ORDER BY invite_interval ASC
  ) AS tmp
set @ct := (select count(1) from tmp(???));
set @row_id := 0;
select avg(invite_interval) as invite_interval_median
from (select * from tmp ORDER BY invite_interval ASC)
where (select @row_id := @row_id + 1)
*/
/*between (how does this work? because row_id will be an int, the other weird stuff is ignored?)
 @ct/2.0 and @ct/2.0 + 1
#how do i incorporate the two sections?
*/
SQL

Checking query status...
