account_summaries_v01.sql 605 B

12345678910111213141516171819202122
  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. ORDER BY statuses.id DESC
  15. LIMIT 20
  16. ) t0
  17. WHERE accounts.suspended_at IS NULL
  18. AND accounts.silenced_at IS NULL
  19. AND accounts.moved_to_account_id IS NULL
  20. AND accounts.discoverable = 't'
  21. AND accounts.locked = 'f'
  22. GROUP BY accounts.id