Instantly share code, notes, and snippets.

@sauloperez /queries.sql Secret
Last active Jan 24, 2018

Embed
What would you like to do?
-- 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