Counting organizations which user has not contacted

I am new at PostgreSQL. I have the following tables created in my schema:

User table:

CREATE TABLE public.users
(
    user_id integer NOT NULL DEFAULT nextval('users_user_id_seq'::regclass),
    first_name character varying(90) COLLATE pg_catalog."default" NOT NULL,
    last_name character varying(90) COLLATE pg_catalog."default" NOT NULL,
    email citext COLLATE pg_catalog."default" NOT NULL,
    user_password character varying(90) COLLATE pg_catalog."default" NOT NULL,
    bt_id integer,
    reset_password_token character varying COLLATE pg_catalog."default",
    bstage_id integer,
    CONSTRAINT users_pkey PRIMARY KEY (user_id),
    CONSTRAINT users_email_key UNIQUE (email),
    CONSTRAINT bstage_id FOREIGN KEY (bstage_id)
        REFERENCES public.business_stage (bstage_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT bt_id FOREIGN KEY (bt_id)
        REFERENCES public.business_type (bt_id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID
)
WITH (
    OIDS = FALSE
)

Organization table:

CREATE TABLE public.organization
(
    org_id integer NOT NULL DEFAULT nextval('organization_org_id_seq'::regclass),
    name character varying(90) COLLATE pg_catalog."default" NOT NULL,
    description character varying(90) COLLATE pg_catalog."default" NOT NULL,
    email citext COLLATE pg_catalog."default" NOT NULL,
    phone_number character varying(11) COLLATE pg_catalog."default" NOT NULL,
    bt_id integer NOT NULL,
    bs_id integer NOT NULL,
    is_active boolean NOT NULL,
    org_link character varying COLLATE pg_catalog."default",
    CONSTRAINT organization_pkey PRIMARY KEY (org_id),
    CONSTRAINT bs_id FOREIGN KEY (bs_id)
        REFERENCES public.business_step (bs_id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID,
    CONSTRAINT bt_id FOREIGN KEY (bt_id)
        REFERENCES public.business_type (bt_id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID
)
WITH (
    OIDS = FALSE
)

And finally Organization_rating table:

CREATE TABLE public.organization_rating
(
    rating integer NOT NULL,
    user_id integer NOT NULL,
    organization_id integer NOT NULL,
    rating_comment character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT organization_rating_pkey PRIMARY KEY (user_id, organization_id),
    CONSTRAINT user_id FOREIGN KEY (user_id)
        REFERENCES public.users (user_id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT stars CHECK (rating >= 1 AND rating < 5)
)
WITH (
    OIDS = FALSE
)

With this schema, an organization has a business type in which gives support to the user that also have that business type. After giving this support, the user can rate this organization with organization_rating. Following this logic, I would like to execute a query which gives me the percentage of the organizations that the user has contacted rated versus the organizations which the user still needs to rate. For example, lets say the user_id 1 rates the organization 1, which have the same business type as the user. 9 more organizations have the same business type, but the user has not rated these organizations. This query would return 10%.

I have the following query to count the organizations which the user has already contacted:

Select U.first_name, COUNT(R.rating)
From users as U INNER JOIN organization_rating as R on U.user_id = R.user_id
Inner join organization as O on O.org_id = R.organization_id
Where O.bt_id = 1 AND R.user_id != 62
Group by U.first_name;

How can I count the organizations which have not been contacted by this user, and the percentage of contacted vs still to be contacted?

If you could also refer me to some sites which can help me learn more of PostgreSQL and the different functions, I would appreciate it. Thank you in advance!

Answer

You can cross join the uses and organizations to get all possible combinations, then bring the bridge table with a left join. The last step is conditional aggregation:

SELECT u.first_name, 
    COUNT(*) FILTER (WHERE r.user_id IS NULL) as cnt_not_contacted,
    AVG((r.user_id IS NOT NULL)::int) as avg_contacted
FROM users u
CROSS JOIN organization o
LEFT JOIN organization_rating r ON u.user_id = r.user_id AND o.org_id = r.organization_id
WHERE o.bt_id = 1 AND u.user_id <> 62
GROUP BY u.user_id

Advertisement

Answer