SQL Calculating the percentage that a user appears in two different tables-CodePudding

users
user_id | created_at.          | sign_up_at           | platform
1       | 2021-01-01 00:01:01  | NULL                 | ios
2       | 2021-01-10 07:13:42  |2021-01-11 08:00:00.  | web
3       | 2021-02-01 12:11:44. | 2021-02-01 13:11:44  | android
4       | 2021-02-28 04:32:12  | 2021-02-28 05:32:12  | ios
5       | 2021-03-22 01:12:11  | 2021-03-22 02:12:11  | android

offers
diagnosis_id | diagnosis_started_at | user_id
1            | 2021-01-11 08:00:00. | 2
2.           | 2021-02-01 13:11:44. | 3
3            | 2021-03-01 05:32:12. | 4
4.           | 2021-03-21 02:12:11. | 10
5.           | 2021-03-23 02:12:11. | 11

Since user 2, 3, and 4 appear in both users and offers, the percentage would equal 60 percent (3/5). How would I write this in SQL?

SELECT *
FROM users a INNER JOIN offers b
ON (a.user_id = b.user_id)

This is the amount of code I have so far where I tried detecting which users appear in both tables. I do not know how to calculate the percentage, though. Any help would be greatly appreciated!

CodePudding user response：

Try

Select (select COUNT( * ) FROM users) *
     (select COUNT(*) 
      FROM users a 
      INNER JOIN offers b
      ON (a.user_id = b.user_id) 
      ) /100 AS Result