Skip to content

Instantly share code, notes, and snippets.

@Danyboy

Danyboy/hours_together.sql

Created January 26, 2018 22:31
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Danyboy/b9fbf14a767367ec1836622f86183092 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Danyboy/b9fbf14a767367ec1836622f86183092 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
hours_together.sql
SELECT id, link, name, hours_together, coef
FROM (
SELECT my_users.user_id, COUNT (*)::float / 12 AS hours_together, COUNT(*)::float / user_coef.their_hours AS coef
FROM user_online LEFT JOIN user_online AS my_users
ON my_users.status = user_online.status
INNER JOIN (SELECT user_id, COUNT(*) AS their_hours
FROM user_online
GROUP BY user_id) AS user_coef
ON user_coef.user_id = my_users.user_id
WHERE user_online.user_id = {$user}
GROUP BY user_online.user_id, my_users.user_id, user_coef.their_hours
ORDER BY hours_together DESC
) AS my_comp
JOIN users
ON (my_comp.user_id = id);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment