Find unique values, count duplicates and rank them using WITH on PostgreSQL 12

Question

I have 3 complex tables. For this question, I will simplify the usages. I need ranking, count (dupes) and unique records (result). It works with single table, however, when another WITH is included and INNER JOIN given, i do not get any records anymore.

Tables:

CREATE TABLE public.emails (
  id                bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
    (MAXVALUE 9223372036854775807),
  sender            jsonb NOT NULL
);


CREATE TABLE public.contacts (
  id                bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
    (MAXVALUE 9223372036854775807),
  email            text NOT NULL,
  full_name            text NOT NULL
);
-- sample data
insert into emails (sender) VALUES ('{"email": "dennis1@example.com", "name": "dennis1"}');
insert into emails (sender) VALUES ('{"email": "dennis1@example.com", "name": "dennis1"}');

insert into contacts (email, full_name) VALUES ('dennis1@example.com', 'dennis1');
insert into contacts (email, full_name) VALUES ('dennis1@example.com', 'dennis1');
insert into contacts (email, full_name) VALUES ('dennis5@example.com', 'dennis5');
insert into contacts (email, full_name) VALUES ('john@example.com', 'john');

Expected Result:

email                   name        rk      count

dennis1@example.com     dennis1     1       4
dennis5@example.com     dennis5     1       1
john@example.com        john        1       1

However, I got 2 issues with this:

1. INNER JOIN makes zero result
2. ORDER BY "count" doesn't work.

What I need?

As you can see tables are different. One table has jsonb column and other is stored as text. So, I separately extract those in each SELECT query then compare.

So What I need is, get all email and names, unique them, count them if they are duplicate and rank. I do not need duplicate entries, but merge them in count.

How can I solve this problem?

Demo

See demo here: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=b79700f74bbf14e190d5f5bf7fcd0670

Answer 1

extract the json & uniting the two datasets before grouping and applying the window function.

WITH united as (
    SELECT email, full_name FROM contacts
    UNION ALL
    SELECT sender->>'email', sender->>'name' FROM emails
)
SELECT
  email
, full_name
, count(*) count, row_number() over (partition by email) rk
FROM united
GROUP BY 1, 2;
        email        | full_name | count | rk
---------------------+-----------+-------+----
 dennis1@example.com | dennis1   |     4 |  1
 dennis5@example.com | dennis5   |     1 |  1
 john@example.com    | john      |     1 |  1
(3 rows)