diff options
| -rw-r--r-- | challenge-209/luca-ferrari/postgresql/ch-2.sql | 31 |
1 files changed, 31 insertions, 0 deletions
diff --git a/challenge-209/luca-ferrari/postgresql/ch-2.sql b/challenge-209/luca-ferrari/postgresql/ch-2.sql new file mode 100644 index 0000000000..0a9dbbdd2a --- /dev/null +++ b/challenge-209/luca-ferrari/postgresql/ch-2.sql @@ -0,0 +1,31 @@ +-- +-- Perl Weekly Challenge 209 +-- Task 2 +-- +-- See <https://perlweeklychallenge.org/blog/perl-weekly-challenge-209/> +-- + +CREATE SCHEMA IF NOT EXISTS pwc209; + +DROP TABLE IF EXISTS pwc209.accounts; +CREATE TABLE IF NOT EXISTS pwc209.accounts ( a_name text, a_email text ); +TRUNCATE TABLE pwc209.accounts; +INSERT INTO pwc209.accounts +VALUES ( 'A', 'a1@a.com' ) +, ('A', 'a2@a.com' ) +, ( 'B', 'b@b.com' ) +, ( 'A', 'a3@a.com' ) +, ( 'A', 'a1@a.com' ); + + +CREATE OR REPLACE FUNCTION +pwc209.task2_plpgsql() +RETURNS TABLE( a text, e text ) +AS $CODE$ + +WITH duplicated_emails AS ( SELECT a_email FROM pwc209.accounts GROUP BY a_email HAVING COUNT(*) > 1 ) +, duplicated_accounts AS ( SELECT a_name FROM pwc209.accounts WHERE a_email IN ( SELECT a_email FROM duplicated_emails ) ) +SELECT distinct( a_name, a_email ) +FROM pwc209.accounts WHERE a_name IN ( SELECT a_name FROM duplicated_accounts ); +$CODE$ +LANGUAGE sql; |