Farcaster

hello farcaster friends

i'm working on improving my sql and i thought it would be fun to practice with real data and answer real questions about the network we're all on 

so, if you've ever wondered something about the farcaster network and the data on it - shoot

examples of questions that i can answer with a sql query:
> what's the daily avg # of casts?
> what % of casts are just text?
> who has been the most mentioned user this week? 

(will answer as many as i can over the next couple of days!)

"how many casts were in channels before and after the changes?"
- @maurelian.eth 

i compared the following time periods:
> oct 4 20:15 UTC (when the changes went into effect) to now
> sep 30 08:15 UTC to oct 4 20:15 UTC (equivalent amount of time)

pre-change: 47.2% of root casts sent into channels
post-change: 22.8% of root casts sent into channels

👉 51.7% drop in root casts sent to channels from pre to post

for full queries and other tidbits, check out:

Pragmatism (Security) at OP Labs.
Prev: ConsenSys & Coinbase.

Attribs: Friendly, curious, introverted, mid-witted.
Chans: /eth-security /maurelian /stoicism




random facts and life excerpts. vulnerability is strength ✨🌈 fullstack & design, building /uno

"how many casts were in channels before and after the changes?"
- @maurelian.eth 

i compared the following time periods:
> oct 4 20:15 UTC (when the changes went into effect) to now
> sep 30 08:15 UTC to oct 4 20:15 UTC (equivalent amount of time)

pre-change: 47.2% of root casts sent into channels
post-change: 22.8% of root casts sent into channels

👉 51.7% drop in root casts sent to channels from pre to post

for full queries and other tidbits, check out:
https://ericagregor.notion.site/How-many-casts-have-been-casted-in-a-channel-since-the-change-compared-to-before-11a870838ed580779da7cf5d4cd03e77?pvs=25

Building Higher apparel at High Line 🧢 
Host at /clickcast /cpg || Husband to @shiwen

i saw your q - i don't have access to session data, i'm just using what is written to the hubs!

Trying to remember if I had a nasty self join in my back pocket

Do casts with images have higher engagement rates than those with just text? What if you hold the user constant?

cryptocurrency 2019 | Interested in the art of generative | AI artist

this is not a question that can be answered by a SQL query 😂

haha in the last week, there have been 340 casts that contain either the string "elon" or "musk"

can't detect gender of users (that data isn't available) and sentiment analysis requires AI - i'm limiting myself to just sql rn :)

diving a bit deeper:

most of the casts mentioning elon seem to be AI generated. the ones that seem real are either talking about:

> recent and upcoming spacex launches
> elon and trump
> replies to memes or pictures of elon

there is also a big chunk of bot casts written in vietnamese this past week:
"thật hay thật tuyệt chúc bạn sức khoẻ để làm công cho elon musk nhé"
which translates to:
"That's great, I wish you good health to work for Elon Musk"

a cast that made me lol while digging around (reply to elon pic):

She/her 
Talking *@! In /extracurriculars reminiscing in /gen-x and curating in /trish

haha in the last week, there have been 340 casts that contain either the string "elon" or "musk"

can't detect gender of users (that data isn't available) and sentiment analysis requires AI - i'm limiting myself to just sql rn :)

diving a bit deeper:

most of the casts mentioning elon seem to be AI generated. the ones that seem real are either talking about:

> recent and upcoming spacex launches
> elon and trump
> replies to memes or pictures of elon

there is also a big chunk of bot casts written in vietnamese this past week:
"thật hay thật tuyệt chúc bạn sức khoẻ để làm công cho elon musk nhé"
which translates to:
"That's great, I wish you good health to work for Elon Musk"

a cast that made me lol while digging around (reply to elon pic):
https://warpcast.com/masky.eth/0x9d924dad

How many times is Elon mentioned and what is the sentiment categorized for gender if/when possible to know 😂

What is the longest palindrome anyone has casted?

Who has the longest streak of days alternating casting and not casting?

Which two users have the most similar following activity, defined as the longest matching sequences of the order in which they follow other users?

Trying to give you some escalating difficulty here :)

top 10 casts by occurrence of anagrams :))

top 10 casts by occurrence of anagrams :))

https://unchained-onchain.xyz/remote-meteor

💭 Your question got 14 responses after 12 hours.

Reward responses directly in the frame. You can also read all of them here: