-- Most commons transactions categories | |
\copy (select count(*) as "number of occurrences", categories.name_translations as "category names" from categories inner join posts on posts.category_id = categories.id inner join transfers on transfers.post_id = posts.id group by categories.id order by "number of occurrences") to 'tmp/exports/most_common_transaction_categories.csv' delimiter ',' csv header; | |
-- Most commons offers (not necessary transacted) | |
\copy (select count(*) as "number of occurrences", categories.name_translations as "category names" from categories inner join posts on posts.category_id = categories.id where type = 'Offer' group by categories.id order by "number of occurrences") to 'tmp/exports/most_common_offers_not_necessary_transacted.csv' delimiter ',' csv header; | |
-- Most common demands added | |
\copy (select count(*) as "number of occurrences", categories.name_translations as "category names" from categories inner join posts on posts.category_id = categories.id where type = 'Inquiry' group by categories.id order by "number of occurrences") to 'tmp/exports/most_common_demands_added.csv' delimiter ',' csv header; | |
-- Age of active users adding offers | |
\copy (select count(*) as "number of occurrences", extract(year from age(date_of_birth)) as years_old from users where date_of_birth is not null group by extract(year from age(date_of_birth)) order by "number of occurrences" desc) to 'tmp/exports/age_of_active_users_adding_offers.csv' delimiter ',' csv header; | |
-- Number of users that provided the date of birth | |
\copy (select count(*) as "number of occurrences" from users where date_of_birth is not null) to 'tmp/exports/number_of_users_that_provided_the_date_of_birth.csv' delimiter ',' csv header; | |
-- Age of most active users giving time on a transaction | |
\copy (select count(*) as "number of occurrences", extract(year from age(date_of_birth)) as age from movements inner join accounts on accounts.id = movements.account_id inner join users on users.id = accounts.accountable_id where accountable_type != 'Organization' and date_of_birth is not null and amount < 0 group by users.id order by "number of occurrences" desc) to 'tmp/exports/age_of_most_active_users_giving_time_on_a_transaction.csv' delimiter ',' csv header; | |
-- Most common amount of time transferred | |
\copy (select count(*) as "number of occurrences", amount * interval '1 sec' as "amount of time" from movements where amount > 0 group by amount having count(*) > 1 order by "number of occurrences" desc limit 10) to 'tmp/exports/most_common_amount_of_time_transferred.csv' delimiter ',' csv header; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment