1
0

account_summaries_v02.sql 643 B

1234567891011121314151617181920212223
  1. SELECT
  2. accounts.id AS account_id,
  3. mode() WITHIN GROUP (ORDER BY language ASC) AS language,
  4. mode() WITHIN GROUP (ORDER BY sensitive ASC) AS sensitive
  5. FROM accounts
  6. CROSS JOIN LATERAL (
  7. SELECT
  8. statuses.account_id,
  9. statuses.language,
  10. statuses.sensitive
  11. FROM statuses
  12. WHERE statuses.account_id = accounts.id
  13. AND statuses.deleted_at IS NULL
  14. AND statuses.reblog_of_id IS NULL
  15. ORDER BY statuses.id DESC
  16. LIMIT 20
  17. ) t0
  18. WHERE accounts.suspended_at IS NULL
  19. AND accounts.silenced_at IS NULL
  20. AND accounts.moved_to_account_id IS NULL
  21. AND accounts.discoverable = 't'
  22. AND accounts.locked = 'f'
  23. GROUP BY accounts.id