Post #3750832

here's the query used. fair warning - it's ugly and takes minutes to complete! SELECT unnest(array[ 'total', 'isSilencedUser', 'isSilencedHost', 'isSuspendedUser', 'isSuspendedHost', 'isPublic', 'isHome', 'isFollowers', 'isSpecified', 'isPoll', 'isMedia', 'isReply', 'isReplyToSelf', 'isBoost', 'isBoostOfSelf', 'isQuote', 'isQuoteOfSelf', 'isChannel', 'isRemote', 'isLocal', 'isLocalOnly' ]) AS "Metric", unnest(array[ to_char("total", '999,999,990'), to_char("isSilencedUser", '999,999,990'), to_char("isSilencedHost", '999,999,990'), to_char("isSuspendedUser", '999,999,990'), to_char("isSuspendedHost", '999,999,990'), to_char("isPublic", '999,999,990'), to_char("isHome", '999,999,990'), to_char("isFollowers", '999,999,990'), to_char("isSpecified", '999,999,990'), to_char("isPoll", '999,999,990'), to_char("isMedia", '999,999,990'), to_char("isReply", '999,999,990'), to_char("isReplyToSelf", '999,999,990'), to_char("isBoost", '999,999,990'), to_char("isBoostOfSelf", '999,999,990'), to_char("isQuote", '999,999,990'), to_char("isQuoteOfSelf", '999,999,990'), to_char("isChannel", '999,999,990'), to_char("isRemote", '999,999,990'), to_char("isLocal", '999,999,990'), to_char("isLocalOnly", '999,999,990') ]) AS "Value", unnest(array[ to_char((("total"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isSilencedUser"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isSilencedHost"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isSuspendedUser"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isSuspendedHost"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isPublic"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isHome"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isFollowers"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isSpecified"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isPoll"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isMedia"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isReply"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isReplyToSelf"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isBoost"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isBoostOfSelf"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isQuote"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isQuoteOfSelf"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isChannel"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isRemote"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isLocal"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isLocalOnly"::float8 / "total"::float8) * 100::float8), '990.00%') ]) AS "Percent", unnest(array[ 'All notes', 'Notes by a silenced user', 'Notes from a silenced instance', 'Notes by a suspended user', 'Notes from a blocked instance', 'Public notes', 'Home-only / unlisted notes', 'Followers-only notes', 'Specified / DM notes', 'Polls', 'Notes w/ media', 'Replies', 'Self-replies', 'Boosts', 'Self-boosts', 'Quotes', 'Self-quotes', 'Notes in a channel', 'Remote notes', 'Local notes', 'Local-only (defederated) notes' ]) AS "Metric" FROM ( SELECT COUNT("id") as total, COUNT("id") FILTER ( WHERE "isSilencedUser" = true ) as "isSilencedUser", COUNT("id") FILTER ( WHERE "isSilencedHost" = true ) as "isSilencedHost", COUNT("id") FILTER ( WHERE "isSuspendedUser" = true ) as "isSuspendedUser", COUNT("id") FILTER ( WHERE "isSuspendedHost" = true ) as "isSuspendedHost", COUNT("id") FILTER ( WHERE "isPublic" = true ) as "isPublic", COUNT("id") FILTER ( WHERE "isHome" = true ) as "isHome", COUNT("id") FILTER ( WHERE "isFollowers" = true ) as "isFollowers", COUNT("id") FILTER ( WHERE "isSpecified" = true ) as "isSpecified", COUNT("id") FILTER ( WHERE "isPoll" = true ) as "isPoll", COUNT("id") FILTER ( WHERE "isMedia" = true ) as "isMedia", COUNT("id") FILTER ( WHERE "isReply" = true ) as "isReply", COUNT("id") FILTER ( WHERE "isReplyToSelf" = true ) as "isReplyToSelf", COUNT("id") FILTER ( WHERE "isBoost" = true ) as "isBoost", COUNT("id") FILTER ( WHERE "isBoostOfSelf" = true ) as "isBoostOfSelf", COUNT("id") FILTER ( WHERE "isQuote" = true ) as "isQuote", COUNT("id") FILTER ( WHERE "isQuoteOfSelf" = true ) as "isQuoteOfSelf", COUNT("id") FILTER ( WHERE "isChannel" = true ) as "isChannel", COUNT("id") FILTER ( WHERE "isLocal" = false ) as "isRemote", COUNT("id") FILTER ( WHERE "isLocal" = true ) as "isLocal", COUNT("id") FILTER ( WHERE "isLocalOnly" = true ) as "isLocalOnly" FROM ( SELECT "note"."id", "user"."isSilenced" as "isSilencedUser", COALESCE("instance"."isSilenced", false) as "isSilencedHost", "user"."isSuspended" as "isSuspendedUser", COALESCE("instance"."isBlocked", false) as "isSuspendedHost", "visibility" = 'public' as "isPublic", "visibility" = 'home' as "isHome", "visibility" = 'followers' as "isFollowers", "visibility" = 'specified' as "isSpecified", "hasPoll" as "isPoll", "fileIds" != '{}' as "isMedia", "replyId" IS NOT NULL as "isReply", "replyUserId" = "userId" as "isReplyToSelf", "renoteId" IS NOT NULL AND ( "text" IS NULL AND "cw" IS NULL AND "replyId" IS NULL AND "hasPoll" = false AND "fileIds" = '{}' ) as "isBoost", "renoteUserId" = "userId" AND ( "text" IS NULL AND "cw" IS NULL AND "replyId" IS NULL AND "hasPoll" = false AND "fileIds" = '{}' ) as "isBoostOfSelf", "renoteId" IS NOT NULL AND NOT ( "text" IS NULL AND "cw" IS NULL AND "replyId" IS NULL AND "hasPoll" = false AND "fileIds" = '{}' ) as "isQuote", "renoteUserId" = "userId" AND NOT ( "text" IS NULL AND "cw" IS NULL AND "replyId" IS NULL AND "hasPoll" = false AND "fileIds" = '{}' ) as "isQuoteOfSelf", "channelId" IS NOT NULL as "isChannel", "userHost" IS NULL as "isLocal", "localOnly" as "isLocalOnly" FROM "note" INNER JOIN "user" on "note"."userId" = "user"."id" LEFT JOIN "instance" on "note"."userHost" = "instance"."host" ) note_types ) type_counts