sql - Postgres most common value query -

i trying figure out how structure queries, , bit lost.

tables:

create table dv_customer(    customer_id integer primary key,    first_name varchar(50),    last_name varchar(50),    email varchar(50),    address_id integer,    active boolean );  create table dv_address(     address_id integer primary key,     address varchar(50),     address2 varchar(50),     district varchar(50),     city_id integer,     postal_code varchar(50),     phone varchar(50) );  create type mpaa_rating enum( 'g', 'pg', 'pg-13', 'r', 'nc-17' );  create table dv_film(     film_id integer primary key,     title varchar(50),     description text,     length smallint,     rating mpaa_rating,     release_year smallint );  create table cb_customers(     last_name varchar(50),     first_name varchar(50),     primary key (last_name, first_name) );  create table cb_books(     title varchar(50),     author_id integer,     edition smallint,     publisher varchar(50),     primary key (title, author_id, edition) );  create table cb_authors(     author_id integer primary key,     first_name varchar(50),     last_name varchar(50) );  create table mg_customers(     customer_id integer primary key,     first_name varchar(50),     last_name varchar(50),     email varchar(50),     address_id integer,     active boolean ); 

i need figure out following queries:

what first , last names of customers live in district having customers?

so far:

select x.first_name, x.last_name     dv_customer x, dv_address y         x.address_id = y.address_id      , (select count(district)     dv_address >= select count(district) dv_address    ); 

what first , last names of top 10 authors when ranked number of books each has written? want author name , book count, in descending order of book count.

so far:

select x.first_name, x.last_name, count(y.title)     cb_authors x, cb_books y     group first_name, last_name     order count(*) desc     limit 10; 

i know these bit of mess, queries can't seem figure out. appreciated. postgres noob , trying figure out how works.

what first , last names of top 10 authors when ranked number of books each has written

this kind of query typically done using window function:

select first_name, last_name, num_books (   select x.first_name, x.last_name,           dense_rank() on (order count(y.title) desc) rnk,           count(*) num_books   cb_authors x     join cb_books y on x.author_id = y.author_id   group x.author_id ) t rnk <= 10 

your clause from cb_authors x, cb_books y missing join condition , creates cartesian join between 2 tables. example on why implicit joins in where clause bad thing. if in habit of using explicit join operator never accidentally miss join condition.

the above uses x.author_id sufficient grouping primary key of column , other (non-grouped) columns in select list functionally dependent on it.