queries.sql

--  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;