I have an SQL question which has me stumped

Question

I'm teaching myself SQL...need help with this question

thanks in advance.

Meta/Facebook Messenger stores the number of messages between users in a table named 'fb_messages'. In this table 'user1' is the sender, 'user2' is the receiver, and 'msg_count' is the number of messages exchanged between them.

Find the top 10 most active users on Meta/Facebook Messenger by counting their total number of messages sent and received. Your solution should output usernames and the count of the total messages they sent or received.

Answer 1

You can use temporary views, using the WITH structure. This works in almost all SQL based DBMS. So it's a quite generic solution. The query that I tested end up like this:

WITH tmp_view AS
  (SELECT id,
          user1 AS userid,
          msg_count
   FROM fb_messages

   UNION 

   SELECT id,
          user2 AS userid,
          msg_count
   FROM fb_messages)

SELECT userid,
       sum(msg_count) as total_msgs
FROM tmp_view
GROUP BY userid
ORDER BY total_msgs
LIMIT 10

Where: The query in WITH creates a temporary view where you aggregate messages sent and received throught two selects, using UNION. Than, you query again over this temp view to achieve the total of each user (sent + received) by using a function SUM grouped by userid and ordering the results.

You can run each select or the entire statement inside WITH separatly to understand bether what is happening.

The syntax of WITH structure varies a litle in different SQL databases, but it's mainly the same. Also the LIMIT syntax can be a litle diferente. The example is written in PostgreSQL.

Certainly there are other ways to do this. But, since this is not a trivial query, probably you cannot do this with one simple statement.