The difference between account and profile creations:

Account creation is the same as registering or creating an account in near.social, and profile creations create a profile or the first modification of an account created in near.social

Definition of Inactive Accounts: An account that does not have any activity after creation (profile creation, profile editing, post creation, likes, etc.)

Account Creations Query: To get account creations, I consider each user's (signer_id) first transaction as account creations using the fact_addkey_events table.

Check this transaction
- Access key (ed2551...TvHU) added for contract social.near with permission to call any methods

Query:

with account_creations_list as (
	select signer_id, min(block_timestamp) as account_creations_date
  		from near.social.fact_addkey_events
  		join near.core.fact_transactions using(tx_hash)
  		where tx_status = 'Success'
  		group by signer_id
)

Profile Creations are the completion of the account created after registering and creating a profile, which includes placement of name, profile photo, etc., to get our list of profile creations at l using the fact_profile_changes table, the first profile change transaction for each account (signer_id) I consider profile creations

Query:

with profile_create_list as (
	select signer_id, block_timestamp, profile_section
  		from near.social.fact_profile_changes
  		join near.core.fact_transactions using(tx_hash)
  		where tx_status = 'Success'
)

Get Post Creations list:

To get list of posts, we use fact_decoded_actions table, provided node='post' and node_data is main type (we have two types of node_data for post, one is main or post, and the other is comment type)

Query

with add_post_list as (
	select tx_hash, block_timestamp, signer_id, node,
  		parse_json(node_data:main) as jsona_main
 		from near.social.fact_decoded_actions
  		join near.core.fact_transactions using(tx_hash)
  		where tx_status = 'Success'
 		and node = 'post' 
  		and jsona_main is not null
)

Get Post Responses or Comments list:

To get list of posts, we use fact_decoded_actions table, provided node='post' and node_data is comment type (we have two types of node_data for post, one is main or post, and the other is comment type)

Query

with add_comment_list as (
	select tx_hash, block_timestamp, signer_id, node,
  		parse_json(node_data:comment) as jsona_comment,
		parse_json(node_data:comment):item:path as jsona_item_path
 		from near.social.fact_decoded_actions
  		join near.core.fact_transactions using(tx_hash)
  		where tx_status = 'Success'
 		and node = 'post' 
  		and jsona_comment is not null
  		and jsona_item_path is not null
)

Getting likes and unlikes for posts and comments:

To get likes and unlikes, we use the fact_decoded_actions table, provided that:

split(try_parse_json(node_data:like):key:path,'/')[2] is not null
For post likes/unlike:
- split(try_parse_json(node_data:like):key:path,'/')[2] = 'main'
For comments likes/unlike:
- split(try_parse_json(node_data:like):key:path,'/')[2] = 'comment'

Query (likes and unlikes for posts)

with like_comment_list as (
	select tx_hash,block_timestamp,signer_id,try_parse_json(node_data:like):value:type as type
 		from near.social.fact_decoded_actions join near.core.fact_transactions using(tx_hash)
  		where tx_status = 'Success' and node_data:like is not null and node = 'index'
  		and split(try_parse_json(node_data:like):key:path,'/')[2] = 'main'
  		and split(try_parse_json(node_data:like):key:path,'/')[2] is not null
)

Query (likes and unlikes for comments)

with like_comment_list as (
	select tx_hash,block_timestamp,signer_id,try_parse_json(node_data:like):value:type as type
 		from near.social.fact_decoded_actions join near.core.fact_transactions using(tx_hash)
  		where tx_status = 'Success' and node_data:like is not null and node = 'index'
  		and split(try_parse_json(node_data:like):key:path,'/')[2] = 'comment'
  		and split(try_parse_json(node_data:like):key:path,'/')[2] is not null
)

Also, to know whether a post or comment has been liked or unliked, we use the following value:

case 
  	when try_parse_json(node_data:like):value:type = 'unlike' then 'Unlike'
  	else 'Like'
end as "Type",

Getting Follows and Unfollows:

To get the list of Follows and Unfollows, we use the fact_decoded_actions table where node = 'graph'. In Follows and Unfollows transactions, in each transaction, an account can follow or Unfollow one or more people at the same time, so we will first describe a Follow/Unfollow transaction:

See this transaction
- { "data": { "wuipod.near": {"graph": {
  "follow": { "readylayerone.near": null, "zavodil.near": null, "mob.near": null, "vlad.near": null, "blaze.near": null, "marieke.near": "", "whendacha.near": "",
  "aliaksandrh.near": "", "partners.learnclub.near": ""
  }
  }
  }
In this transaction, the wuipod.near account has followed 4 accounts marieke.near, whendacha.near, aliaksandrh.near and partners.learnclub.near, and unfollowed 5 accounts blaze.near, vlad.near, mob.near, zavodil.near and readylayerone.near at the same time.
So we first split this list using a ',', then we get an array that includes the follow and unfollow list, then we split this list using the flatten (input => follow_array) command
Finally, the list of follow and unfollow is obtained as follows:
- Unfollow: flatten value ilike '%null%'
- Follow: flatten value not ilike '%null%'
See the full query here

Getting the Widget Creation list:

To get the Widget Creation list, we use the fact_decoded_actions table, provided that the node ilike '%widget%'

Query

with widget_creation_list as (
	select   signer_id,key as widget,min(block_timestamp) as widget_creations_date  
        from near.social.fact_decoded_actions 
  		join near.core.fact_transactions using(tx_hash),
  		table(flatten(input => node_data))
		where node ilike '%widget%'
		group by signer_id, widget 
)

NEAR - Social Dashboard