FB questions

Question 1:
We would like to report for each employee - 'percentage of employees who joined the same department after the employee', 'percentage of company that joined after the employee', manager's name “in the same SQL query”.
Write the SQL in 3 or more ways. You can assume the database is either of them - mySQL, Postgres or Oracle syntax


Question 2:
Table: movies_watched
----------------
userid | movieId
1      | A
1      | B
1      | C
2      | A
2      | D
3      | W
3      | E
7      | T
.
.
 
Table: users
----------------
userid | FriendId
1      | 2
1      | 8
2      | 9
5      | 2
8      | 3

Question: recommend to each user movies based on -- movies viewed by his/her friends

Expcted O/p

1  -> A, D


SQL: Select max (x), userid, movieid from (
select user, count(*) as x from movies_watched group by user)) a  left join users.userid =  a.userid
A
USER COUNT#
1    5
2    6
B
USER ID, COUNT, MOVIE ID

C
USERID FRIEND ID 
query#1
SELECT DISTINCT USERID, FRIENDID, MOVIEID FROM MOVIESWATCHED A INNER JOIN USERS B ON A.USERID = B.USERID AND A.USERID = 1

SELECT USERID, FRIENDID, MOVIEID FROM USERS A  LEFT OUTER JOIN MOVIESWARCHED B ON A.USERID = B.USERID

------query#1
USERID   FRIENDID  MOVIEID
1        2        A
1        8        B

======
=================================================

fruit_sales
+------------+--------+------+
| dateid     | fruit | sold |
+------------+--------+------+
| 2015-01-01 | Apple | 31 |
| 2015-01-01 | Orange | 19 |
| 2015-01-02 | Apple | 37 |
| 2015-01-02 | Orange | 26 |
| 2015-01-03 | Apple | 21 |
| 2015-01-03 | Orange | 21 |
| 2016-01-04 | Apple | 35 |
| 2017-01-01 | Banana | 40 |
| 2014-01-01 | Kiwi | 4 |
| 2017-01-02 | Banana | 61 |
| 2017-01-02 | Kiwi | 12 |
| 2016-01-03 | Banana | 55 |
| 2016-01-03 | Kiwi | 9 |
| 2017-01-04 | Banana | 48 |
| 2017-01-04 | Kiwi | 5 |
+------------+--------+------+

Expected output: 

                            FRUITS
            -------------------------------------               
Year        Apple     Orange      Banana      Kiwi

2014        0         0           0           4
2015        
2016
2017
...
...
..

SQL
----------

SELECT YEAR(DATEID), DECODE (FRUIT, 1, VALUE) 
FROM (SELECT YEAROF(DATEID), FRUIT , SUM(*) FROM FRUIT_SALES GROUP BY YEAROF(DATEID), FRUIT) A

>>temporry tabe A
YEAR FRUIT SUM
2014 APPLE    1
2014 baNana    150
2014 orange    53

Year        Apple     Orange      Banana      Kiwi  

2014        0         54          150          4