with posts as (

select

tx_hash

,block_timestamp

,signer_id

,lower(post_text) as lowercased_posts

from near.social.fact_posts

where block_timestamp > '2024-09-01'

and post_text is not null

and post_text != ''

and post_text != ' '

),

cortexing as (

select

tx_hash

,block_timestamp

,signer_id

,snowflake.cortex.classify_text(lowercased_posts, ['bot', 'human']) as classified_posts

,lowercased_posts

from posts

select

signer_id

,snowflake.cortex.classify_text(classified_posts, ['bot', 'human']) as user_classification

,count(tx_hash) as posts

from cortexing

group by 1, 2

order by posts desc