# 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;

​