Write a query to display the patron ID and the average number of days that patron keeps books during a checkout. Limit the results to only patrons who have at least three checkouts. Sort the results in descending order by the average days the book is kept.
时间: 2024-03-11 17:43:41 浏览: 84
rubygem-patron-doc-0.4.20-3.el7.noarch.rpm
Sure, here's the SQL query:
```
SELECT patron_id, AVG(DATEDIFF(checkin_date, checkout_date)) AS avg_days
FROM checkouts
GROUP BY patron_id
HAVING COUNT(*) >= 3
ORDER BY avg_days DESC;
```
This query selects the patron ID and the average number of days that patron keeps books during a checkout from the `checkouts` table. It then groups the results by patron ID and only includes patrons who have at least three checkouts (specified by the `HAVING` clause). Finally, it sorts the results in descending order by the average number of days the book is kept (specified by the `ORDER BY` clause).
阅读全文