-
Notifications
You must be signed in to change notification settings - Fork 320
/
ch-2.sql
31 lines (26 loc) · 905 Bytes
/
ch-2.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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;