Question: I need help creating a complex SQL query. Consider the following tables (simplified for example):

**lists**

```
list_id | user_id | list_type
-----------------------------------
1       | 1       | favorite movies
2       | 2       | favorite movies

```

**list\_items**

```
list_id | movie_id 
------------------
1       | 1
2       | 1
2       | 2

```

In this example user 1 has added movie 1 to his favorite movies list. User 2 has added movie 1 and movie 2 to his favorite movies list. 

I'm trying to output a list of all other movies every user who added movie 1 also added to their favorite movies lists, ordered by frequency. The result should therefore be:

```
movie_id | total_adds
---------------------
2        | 1

```

I'm having issues grasping how I can use COUNT and DISTINCT to generate this output and if I can JOIN somehow to reduce the processing overhead.

Answer: 
Here is one way to do this.

```
with
     test_data ( user_id, movie_id ) as (
       select 1, 1 from dual union all
       select 2, 1 from dual union all
       select 2, 2 from dual union all
       select 2, 3 from dual union all
       select 3, 2 from dual union all 
       select 3, 3 from dual union all
       select 4, 1 from dual union all
       select 4, 2 from dual union all
       select 4, 9 from dual
     )
--  End of test data (NOT part of the SQL query).
--  Solution (SQL query) begins BELOW THIS LINE. Use your actual table and column names.
select   movie_id, count(*) as ct
from     test_data
where    user_id in ( select user_id from test_data where movie_id = 1 )
  and    movie_id != 1
group by movie_id
order by ct desc, movie_id
;

  MOVIE_ID         CT
---------- ----------
         2          2
         3          1
         9          1

4 rows selected.

```
Here is another way. It is a bit more complex, but depending on the data will usually be faster:

```
WITH lists ( list_id, user_id ) AS (
      SELECT 1, 1 FROM dual UNION ALL
      SELECT 2, 2 FROM dual UNION ALL
      SELECT 3, 2 FROM dual UNION ALL
      SELECT 4, 2 FROM dual UNION ALL
      SELECT 5, 3 FROM dual UNION ALL 
      SELECT 6, 3 FROM dual UNION ALL
      SELECT 7, 4 FROM dual UNION ALL
      SELECT 8, 4 FROM dual UNION ALL
      SELECT 9, 4 FROM dual
  ), list_items (list_id, movie_id) AS (
      SELECT 1, 1 FROM dual UNION ALL
      SELECT 2, 1 FROM dual UNION ALL
      SELECT 3, 2 FROM dual UNION ALL
      SELECT 4, 3 FROM dual UNION ALL
      SELECT 5, 2 FROM dual UNION ALL 
      SELECT 6, 3 FROM dual UNION ALL
      SELECT 7, 1 FROM dual UNION ALL
      SELECT 8, 2 FROM dual UNION ALL
      SELECT 9, 9 FROM dual
  ), test_data (user_id, movie_id) AS (
     SELECT user_id, movie_id FROM lists l
     JOIN list_items li ON l.list_id=li.list_id
  )
SELECT movie_id, count(*) ct
FROM (
   SELECT user_id, movie_id, min(movie_id) OVER (PARTITION BY User_Id) min_movie_id
      , SUM(CASE WHEN movie_id = 1 THEN 0 ELSE 1 END) OVER (PARTITION BY user_id) ct
   FROM test_data 
)
WHERE min_movie_id=1 AND movie_id<>1
GROUP BY movie_id
ORDER BY ct desc, movie_id
;

```

Test data expanded back to original tables. The results are the same as mathguy's.