microblog.pub : Where do my Followers and Following come from?

I run the microblog.pub as my federated ActivityPub server. It's written in Python and uses SQLite as the database to store the data. I am very comfortable with both of these and also like them a lot as they are very approachable technologies.

I recently took a backup of the database, just downloading a copy of the SQLite DB file. Since I had almost reached 100+ followers and following, I was curious about the diversity or spread of instances. So I wrote some SQL queries. microblog.pub has three relevant tables 

• actor - This stores all the actors involved in some activity with your instance.
• following - All my following
• followers - All my followers

The user ids are stored in either "ap_actor_id" or "ap_id", which is of the form "https://tatooine.club/users/nemo". I wanted to extract only the domains. So something between "https://" and the third "/". So here are the queries to get the domain name and number of subscribes from that domain. I have some tree maps (just for following and followers) to visualize the numbers. You can draw your own conclusions.

Followers

select substr(ap_actor_id, start_pos+3, end_pos-1 ) as domain, 
		count(*) as followers_count_by_domain from (
		select *,  IIF(instr(s,'/'), instr(s,'/'), length(s)) as end_pos from (
			select *, substr(ap_actor_id, start_pos+3) as s	from (	
				SELECT *, instr(ap_actor_id,'://') AS start_pos FROM follower
		)
	)
)
group by domain
order by followers_count_by_domain desc

Following

select substr(ap_actor_id, start_pos+3, end_pos-1 ) as domain, 
    count(*) as following_count_by_domain from (
    select *,  IIF(instr(s,'/'), instr(s,'/'), length(s))  AS end_pos from (
        select *, substr(ap_actor_id, start_pos+3) as s from (  
                    SELECT *, instr(ap_actor_id,'://') AS start_pos FROM following
        )
    )
)
group by domain
order by following_count_by_domain desc

Actors

select substr(ap_id, start_pos+3, end_pos-1 ) as domain, 
    count(*) as actor_count_by_domain from (
    select *,  IIF(instr(s,'/'), instr(s,'/'), length(s))  AS end_pos from (
        select *, substr(ap_id, start_pos+3) as s   from (  
                    SELECT *, instr(ap_id,'://') AS start_pos FROM actor
        )
    )
)
group by domain
order by actor_count_by_domain desc

Note:

I also realized I could have queried the handle of the form "@[email protected]", which would have been an easy query to write. Regardless now you know how to extract base domain from a URL in SQL.