Skip to content

Instantly share code, notes, and snippets.

@maratuska
Last active February 21, 2023 09:45
Show Gist options
  • Save maratuska/db9e5d0b36f3eeb4c14265f75ea0f92c to your computer and use it in GitHub Desktop.
Save maratuska/db9e5d0b36f3eeb4c14265f75ea0f92c to your computer and use it in GitHub Desktop.
WITH corp_payments AS (
SELECT
p.member_no,
c.corp_no,
c.corp_name,
p.payment_amt
FROM `member` m
JOIN payment p
ON m.member_no = p.member_no
RIGHT JOIN corporation c
ON m.corp_no = c.corp_no
)
SELECT
cp.corp_name,
COUNT(cp.member_no) members_count,
ROUND(AVG(cp.payment_amt), 4) payment_avg
FROM corp_payments cp
GROUP BY cp.corp_no
ORDER BY members_count DESC, payment_avg DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment