-- Database schema
| authors : au_id [ TEXT ] primary_key , au_lname [ TEXT ] , au_fname [ TEXT ] , phone [ TEXT ] , address [ TEXT ] , city [ TEXT ] , state [ TEXT ] , zip [ TEXT ] , contract [ TEXT ] | jobs : job_id [ INTEGER ] primary_key , job_desc [ TEXT ] , min_lvl [ INTEGER ] , max_lvl [ INTEGER ] | publishers : pub_id [ TEXT ] primary_key , pub_name [ TEXT ] , city [ TEXT ] , state [ TEXT ] , country [ TEXT ] | employee : emp_id [ TEXT ] primary_key , fname [ TEXT ] , minit [ TEXT ] , lname [ TEXT ] , job_id [ INTEGER ] employee.job_id = jobs.job_id , job_lvl [ INTEGER ] , pub_id [ TEXT ] employee.pub_id = publishers.pub_id , hire_date [ DATETIME ] | pub_info : pub_id [ TEXT ] primary_key pub_info.pub_id = publishers.pub_id , logo [ BLOB ] , pr_info [ TEXT ] | stores : stor_id [ TEXT ] primary_key , stor_name [ TEXT ] , stor_address [ TEXT ] , city [ TEXT ] , state [ TEXT ] , zip [ TEXT ] | discounts : discounttype [ TEXT ] , stor_id [ TEXT ] discounts.stor_id = stores.stor_id , lowqty [ INTEGER ] , highqty [ INTEGER ] , discount [ REAL ] | titles : title_id [ TEXT ] primary_key , title [ TEXT ] , type [ TEXT ] , pub_id [ TEXT ] titles.pub_id = publishers.pub_id , price [ REAL ] , advance [ REAL ] , royalty [ INTEGER ] , ytd_sales [ INTEGER ] , notes [ TEXT ] , pubdate [ DATETIME ] | roysched : title_id [ TEXT ] roysched.title_id = titles.title_id , lorange [ INTEGER ] , hirange [ INTEGER ] , royalty [ INTEGER ] | sales : stor_id [ TEXT ] sales.stor_id = stores.stor_id , ord_num [ TEXT ] , ord_date [ DATETIME ] , qty [ INTEGER ] , payterms [ TEXT ] , title_id [ TEXT ] sales.title_id = titles.title_id | titleauthor : au_id [ TEXT ] titleauthor.au_id = authors.au_id , title_id [ TEXT ] titleauthor.title_id = titles.title_id , au_ord [ INTEGER ] , royaltyper [ INTEGER ] |
-- -- Name the top five titles that sold more than average and list them in descending order of the number of sales in California stores?

SELECT T1.title FROM titles AS T1 INNER JOIN sales AS T2 ON T1.title_id = T2.title_id INNER JOIN publishers AS T3 ON T1.pub_id = T3.pub_id WHERE T2.qty > ( SELECT CAST(SUM(qty) AS REAL) / COUNT(title_id) FROM sales ) AND T3.state = 'CA' ORDER BY T2.qty DESC LIMIT 5;