最近面试遇到的几道SQL题，写好SQL还是很重要的。新人求加米

紧张，第二题就被卡住了。其实不难，下次再来
1. How many checkouts were there each week?

2. In which week were JK Rowling books most popular?

3. Which is the most popular book each month?


Schema
                       Table "public.library_checkouts"
      Column      |            Type             | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+---------
bibnumber        | character varying           |           |          |
itembarcode      | character varying           |           |          |
itemtype         | character varying           |           |          |
collection       | character varying           |           |          |
callnumber       | character varying           |           |          |
checkoutdatetime | timestamp without time zone |           |          |

                       Table "public.item_info"
     Column      |       Type        | Collation | Nullable | Default
-----------------+-------------------+-----------+----------+---------
bibnum          | character varying |           |          |
title           | character varying |           |          |
author          | character varying |           |          |
isbn            | character varying |           |          |
publicationyear | character varying |           |          |
publisher       | character varying |           |          |
subjects        | character varying |           |          |
itemtype        | character varying |           |          |
itemcollection  | character varying |           |          |
floatingitem    | character varying |           |          |
itemlocation    | character varying |           |          |
reportdate      | character varying |           |          |
itemcount       | character varying |           |          |

2. In which week were JK Rowling books most popular?
select week from (
select author, count(*) from joined table
group by year(time), week(time), author
having author=max(count(author)))
or use window function rank()

1. How many checkouts were there each week?
select count(*) from table1
group by week(time), year(time)

lz这申请的是SDE的工作吗？

不是。申请的是DS工作

可以用window function..

3. select title from jointed_table
select week, title from (
select author, title, count(*) from joined table
group by year(time), week(time), title
having author=max(count(title))limit 1)


My thoughts