RDizzl3 RDizzl3 - 6 months ago 10
SQL Question

Create Efficient Query in PostgreSQL and Consolidate Statements

Please excuse me if this is trivial - I am new to using PostGRESQL. I am given two
tables:

CREATE TABLE group_users (
user_id int NOT NULL PRIMARY KEY,
group_id int NOT NULL,
join_time timestamp NOT NULL,
member_rank enum('l','a','m') DEFAULT NULL
);

CREATE TABLE interactions (
interactionid int PRIMARY KEY NOT NULL,
user_id int NOT NULL,
target_user_id int NOT NULL,
start_time timestamp NOT NULL
);


I now have to answer the question:

What is the percentage of interactions between: users in different groups,
users in groups vs. those not in any group, and users in the same
group?

This is my query - I do, however, have to consolidate it into one query.

My questions
are:


  1. For the query below is there a more efficient way to do this in PostGRESQL?

  2. What would be the most efficient way to make this one query?



-- Map user_ids to group_ids with interaction information

CREATE TEMPORARY TABLE alliance_battle_information AS
SELECT
a.interactionid,
a.user_id,
b.group_id,
a.target_user_id,
c.group_id AS target_group_id
FROM interactions a
LEFT JOIN
group_users b
on a.user_id = b.user_id
LEFT JOIN
group_users c
ON a.target_user_id = c.user_id;


-- Percentage of interactions of users in difference groups

select
(select count(*) from group_interaction_information
where group_id != target_group_id and group_id is not null
and target_group_id is not null)/(select count(*) from group_interaction_information)::float;


-- Percentage of interactions from users in groups

select
(select count(*) from group_interaction_information
where group_id is not null)/
(select count(*) from group_interaction_information)::float;


-- Percentage of interactions from users not in a group

select
(select count(*) from group_interaction_information where group_id is null)/
(select count(*) from group_interaction_information)::float;


-- Percentage of interactions from users in the same group

select
(select count(*) from group_interaction_information
where group_id = target_group_id and group_id is not null
and target_group_id is not null)/(select count(*) from group_interaction_information)::float;

Answer

One way to combine your queries is with conditional aggregation:

select 
    count(case when group_id <> target_group_id then 1 end) / count(*)::float,
    count(case when group_id is not null then 1 end) / count(*)::float,
    count(case when group_id is null then 1 end) / count(*)::float,
    count(case when group_id = target_group_id then 1 end) / count(*)::float
from group_interaction_information

By the way, a = b will never be true if one or both of the columns are null, so if you have a condition like group_id = target_group_id it's not necessary to specify and target_group_id is not null. The same applies to a <> b. To test run select null = null, null <> null, 'a' <> null - these will all result in null.