Back to Code Snippets

Michael Hunger

@jexp

‌

StackOverflow Analytics

Editor's note: Michael shares Stackoverflow data in MotherDuck as part of this snippet as well as typical aggregate analytics on the data. There are additional sample data sets attached by default in MotherDuck as the 'sample_data' share.

Attach and select MotherDuck database

Data shared/available on MotherDuck

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

Copy code

Which 5 questions have the most comments, what is the post title and comment countSQL

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

Copy code

User reputation and reputation rate per daySQL

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

Copy code

Reputation rate as bar chart with CTESQL

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

Copy code

Post statistics per yearSQL

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

Copy code

Posting Frequency with bar chart on Weekdays for "sql" tagSQL

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

Copy code

Posting Frequency with bar chart on Weekdays for "rust" tagSQL

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

Copy code

Show less
Michael Hunger

Expand

Share link

Edited 02/23/24
‌

Quick inline bar charts with bar() functionSQL

Execute this SQL

-- bar(x, min, max[, width])
--- Draw a band whose width is proportional to (x - min) and equal to width characters when x = max. width defaults to 80.

with data as (select unnest(range(1,10)) as v order by random()) 

select bar(v,minv,maxv,30) from data, 
       (select min(v) as minv, max(v) as maxv from data);

-- ┌────────────────────────────────┐
-- │     bar(v, minv, maxv, 30)     │
-- │            varchar             │
-- ├────────────────────────────────┤
-- │ ██████████████████████▌        │
-- │ ███▊                           │
-- │ ██████████████████████████████ │
-- │                                │
-- │ ███████████████                │
-- │ ███████████▎                   │
-- │ ██████████████████▊            │
-- │ ██████████████████████████▎    │
-- │ ███████▌                       │
-- └────────────────────────────────┘
Copy code

Copy code

Michael Hunger

Copy code

Expand

Share link

Created 07/15/23
‌

Sort by WeekdaysSQL

Execute this SQL

-- duckdb has only weekday-name not number
-- create enum in the right order
create type Weekday as enum ('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday');

-- cast the string to enum for sorting
select count(*), cast(dayname(CreationDate) as Weekday) as day
from posts where posttypeid = 1 and tags like '%>sql<%'
group by all
order by day asc;
-- doing the cast in order by causes a bug

-- ┌──────────────┬───────────┐
-- │ count_star() │    day    │
-- │    int64     │  weekday  │
-- ├──────────────┼───────────┤
-- │       103937 │ Monday    │
-- │       115575 │ Tuesday   │
-- │       119825 │ Wednesday │
-- │       119514 │ Thursday  │
-- │       103445 │ Friday    │
-- │        47139 │ Saturday  │
-- │        47390 │ Sunday    │
-- └──────────────┴───────────┘
Copy code

Copy code

Michael Hunger

Copy code

Expand

Share link

Created 07/15/23
‌

Rounding integer numbers with trailing zerosSQL

Execute this SQL

select round(2061899,-6); -- 2000000.0

Copy code

Copy code

Michael Hunger

Copy code

Expand

Share link

Created 07/15/23
Terms of Service
Privacy Policy
Sponsored by