Skip to content

Instantly share code, notes, and snippets.

@nerestaren

nerestaren/select usuaris.sql

Created December 6, 2016 23:58
Show Gist options
  • Save nerestaren/ce2a34c16a4cffe2db5f68fcae8d72d0 to your computer and use it in GitHub Desktop.
Save nerestaren/ce2a34c16a4cffe2db5f68fcae8d72d0 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
select usuaris.sql
SELECT username,
user_posts AS sempre,
(SELECT COUNT(post_id) FROM posts WHERE poster_id = user_id AND post_time < UNIX_TIMESTAMP() - 7776000) AS 'des de fa més de tres mesos',
(SELECT COUNT(post_id) FROM posts WHERE poster_id = user_id AND post_time BETWEEN UNIX_TIMESTAMP() - 7776000 AND UNIX_TIMESTAMP() - 5184000) AS 'des de fa tres mesos fins fa dos',
(SELECT COUNT(post_id) FROM posts WHERE poster_id = user_id AND post_time BETWEEN UNIX_TIMESTAMP() - 5184000 AND UNIX_TIMESTAMP() - 2592000) AS 'des de fa dos mesos fins fa un',
(SELECT COUNT(post_id) FROM posts WHERE poster_id = user_id AND post_time >= UNIX_TIMESTAMP() - 2592000) AS 'des de fa un mes fins avui',
(SELECT pf_padri FROM profile_fields_data WHERE profile_fields_data.user_id = users.user_id) AS 'padri'
FROM users WHERE user_posts > 0
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment