Skip to content

Instantly share code, notes, and snippets.

@thiagodiniz

thiagodiniz/month_sequence.sql

Created May 25, 2020 20:16
Show Gist options
  • Save thiagodiniz/fb9b15d9e8ff6ea1e130a34b58717044 to your computer and use it in GitHub Desktop.
Save thiagodiniz/fb9b15d9e8ff6ea1e130a34b58717044 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
month_sequence.sql
SELECT
users.email, gateway_wallets.id as gateway_wallet_id,
count(payments.id) FILTER (WHERE total_amount_in_cents > 25000) AS payments_count_min,
count(payments.id) FILTER (WHERE total_amount_in_cents <= 25000) AS payments_count,
((count(payments.id) FILTER (WHERE total_amount_in_cents > 25000) * 8) +
(count(payments.id) FILTER (WHERE total_amount_in_cents <= 25000) * 3)) as cost
FROM payments
left outer join gateway_wallets on gateway_wallets.id = payments.gateway_wallet_id
left outer join users on users.id = gateway_wallets.user_id
where payments.aasm_state = 'pending'
group by gateway_wallets.id, users.email;
SELECT
users.email, gateway_wallets.id as gateway_wallet_id,
coalesce(SUM(paid_amount_in_cents)::float/100,0) as paid_amount,
count(payments.id) FILTER (WHERE total_amount_in_cents > 25000) AS payments_count_min,
count(payments.id) FILTER (WHERE total_amount_in_cents <= 25000) AS payments_count,
((count(payments.id) FILTER (WHERE total_amount_in_cents > 25000) * 8) +
(count(payments.id) FILTER (WHERE total_amount_in_cents <= 25000) * 3)) as transaction_cost,
(coalesce(SUM(paid_amount_in_cents)::float/100,0) + ((count(payments.id) FILTER (WHERE total_amount_in_cents > 25000) * 8) +
(count(payments.id) FILTER (WHERE total_amount_in_cents <= 25000) * 3))) AS expected_balance
FROM payments
left outer join gateway_wallets on gateway_wallets.id = payments.gateway_wallet_id
left outer join users on users.id = gateway_wallets.user_id
where payments.aasm_state in ('paid', 'processing', 'pending')
group by gateway_wallets.id, users.email;
SELECT
date_trunc('month', payments.due_at)::DATE AS payment_due_date,
parties.user_id AS user_id,
users.email AS user_email,
count(*) AS payment_count,
users.legal_name, '''' || users.tax_document AS tax_document,
sum(payments.total_amount_in_cents)::float/100 AS amount_transacted,
contracts_active.contract_count
FROM payments
JOIN contracts ON contracts.id = payments.contract_id
JOIN parties ON contracts.id = parties.contract_id
JOIN users ON users.id = parties.user_id
LEFT JOIN LATERAL (
SELECT parties.user_id, count(*) as contract_count
FROM contracts
JOIN parties ON contracts.id = parties.contract_id
WHERE contracts.aasm_state IN ('active', 'active_without_payment')
AND parties.role = 'owner'
AND payment_due_date BETWEEN contracts.starts_at AND contracts.expires_at
GROUP BY parties.user_id
) contracts_active ON contracts_active.user_id = users.id
WHERE parties.role IN ('owner')
AND payments.aasm_state IN ('pending', 'paid', 'processing', 'overdue', 'transferring', 'deposited')
AND users.email NOT IN ('david@hent.com.br', 'pedro.costa@hent.com.br')
GROUP BY users.email, parties.user_id, date_trunc('month', payments.due_at)::DATE, users.legal_name, users.tax_document, contracts_active.contract_count
ORDER BY 1, 2;
SELECT parties.user_id, count(*) as contract_count
FROM contracts
JOIN parties ON contracts.id = parties.contract_id
WHERE contracts.aasm_state IN ('active', 'active_without_payment')
AND parties.role = 'owner'
AND NOW() BETWEEN contracts.starts_at AND contracts.expires_at
GROUP BY parties.user_id;
select date_trunc('month', '2017-01-20')::DATE ;
generate_series('2017-01-01', now(), '1 month'), generate_series('2017-01-30', now(), '1 month')
date_trunc('month', payments.due_at)::DATE ;
select * from payments where id=2230
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment