Source

onpython3yet / migrations / 001-view_most_required_packages.sql

Full commit
-- packages that most required to move to Python 3.
--
-- In addition to Package columns an extra column called
-- required_count is available.  This is an integer indicating the
-- number of times *other* packages required it.
--
-- The query is ordered by required_count in descending order.
-- For example, if both foo and bar depend on Django then
-- Django gets a required_count of 2 and is at the
-- top of the list.

create or replace view view_most_required_packages as
    select  p.name,
            count(pr.id) as required_count
    from    packages_package p
            left join packages_packagerequirement pr on
                                (p.name=pr.required_package_id)
    where   p.supports_python_3 = 'f'
    group by p.name
    order by count(pr.id) desc, p.name;