ATTACH 'md:_share/stackoverflow/6c318917-6888-425a-bea1-5860c29947e5' AS stackoverflow;
USE stackoverflow;
Copy code

SELECT Title, CommentCount
FROM posts
WHERE PostTypeId = 1
ORDER BY CommentCount DESC
LIMIT 5;
Copy code

SELECT name, reputation, 
       round(reputation/day(today()-createdAt)) as rate, 
       day(today()-createdAt) as days, 
       createdAt
FROM users WHERE reputation > 1000000 ORDER BY rate DESC;
Copy code

WITH top_users as (
  SELECT name, reputation, 
       round(reputation/day(today()-createdAt)) as rate, day(today()-createdAt) as days, 
       createdAt
       FROM users WHERE reputation > 1000000
)
SELECT name, reputation, rate, bar(rate,150,300,35) AS bar FROM top_users;
Copy code

SELECT  year(CreationDate) AS year, count(*), 
        round(avg(ViewCount)), max(AnswerCount)
FROM posts 
GROUP BY year 
ORDER BY year DESC LIMIT 10;
Copy code

SELECT count(*) as freq, dayname(CreationDate) AS day, 
       bar(freq, 0, 150000,20) AS plot
FROM posts WHERE posttypeid = 1 
AND tags LIKE '%<sql>%'
GROUP BY all 
ORDER BY freq DESC;
Copy code

SELECT count(*) as freq, dayname(CreationDate) AS day, 
       bar(freq, 0, 10000,20) AS plot
FROM posts WHERE posttypeid = 1 
AND tags LIKE '%<rust>%'
GROUP BY all 
ORDER BY freq DESC;
Copy code

/* division, title, date, notes, bunting */
select
    division,
    unnest(events.events, recursive:=true)
from (
    unpivot 'https://www.gov.uk/bank-holidays.json'
    on "england-and-wales", "scotland", "northern-ireland"
    into name division value events
)
Copy code