The flavor of SQL is Postgres SQL.
Each query should usually start with SELECT * from profiles
or SELECT * from casts
.
WHERE casts.text ILIKE '%opensea%'
order by custom_metrics->'custom_cast_metrics'->'new' desc
order by custom_metrics->'custom_cast_metrics'->'hot' desc
WHERE EXISTS (SELECT 1 FROM jsonb_array_elements(og->'urls') WHERE value->'tags' ? 'video')
WHERE reply_parent_hash is NULL
where exists (select * from casts casts_subquery where casts_subquery.reply_parent_hash=casts.hash and casts_subquery.text ilike '%@event%')
where author_fid in (select f.follower_fid from following f, profiles p where p.username='jem'::text and f.following_fid=p.fid)
WHERE published_at > NOW() - INTERVAL '7 days'
CREATE TABLE casts (
"hash" text NOT NULL PRIMARY KEY,
"type" text,
"published_at" timestamptz,
"username" text,
"text" text,
"reply_parent_hash" text,
"reply_parent_fid" int8,
"author_fid" int8,
"signature" text,
"thread_hash" text,
"display_name" text,
"avatar_url" text,
"avatar_verified" bool,
"mentions" jsonb,
"reactions" int8,
"recasts" int8,
"watches" int8,
"replies" int8,
"is_recast" bool,
"recasted_cast_hash" text,
"recasters" jsonb,
"deleted" bool,
"custom_metrics" jsonb
"reply_to_data" jsonb
"recast_data" jsonb,
"weighted_keywords" tsvector,
"og" jsonb,
CONSTRAINT hash_unique UNIQUE(hash)
);
CREATE TABLE profiles (
"fid" int8 NOT NULL PRIMARY KEY,
"address" text NOT NULL,
"username" text,
"display_name" text,
"avatar_url" text,
"avatar_verified" bool,
"followers" int8,
"following" int8,
"bio" text,
"referrer" text,
"registered_at" timestamptz,
"updated_at" timestamptz DEFAULT now(),
"custom_metrics" jsonb,
"weighted_keywords" tsvector,
CONSTRAINT username_unique UNIQUE(username),
CONSTRAINT address_unique UNIQUE(address)
);
CREATE TABLE following(
"follower_fid" int8 not null,
"following_fid" int8 not null,
"created_at" timestamptz not null DEFAULT now()
);
CREATE TYPE SENTIMENT_ENTITY_TYPE AS ENUM ('PERSON',
'LOCATION',
'ORGANIZATION',
'FACILITY',
'BRAND',
'COMMERCIAL_ITEM',
'MOVIE',
'MUSIC',
'BOOK',
'SOFTWARE',
'GAME',
'PERSONAL_TITLE',
'EVENT',
'DATE',
'QUANTITY',
'ATTRIBUTE',
'OTHER');
CREATE TYPE SENTIMENT AS ENUM('POSITIVE',
'NEGATIVE',
'NEUTRAL',
'MIXED');
CREATE TYPE ENTITY_TYPE AS ENUM ('COMMERCIAL_ITEM', 'DATE', 'EVENT', 'LOCATION', 'ORGANIZATION', 'OTHER', 'PERSON', 'TITLE', 'QUANTITY');
CREATE TABLE sentiment_entity_mentions(
entity_type SENTIMENT_ENTITY_TYPE not null,
entity_text text not null,
cast_hash text not null references casts(hash),
score numeric,
group_score numeric,
begin_offset integer,
end_offset integer,
sentiment SENTIMENT not null,
sentiment_score_mixed numeric,
sentiment_score_negative numeric,
sentiment_score_neutral numeric,
sentiment_score_positive numeric
);
create table if not exists entity_mentions(
entity_type ENTITY_TYPE not null,
entity_text text not null,
cast_hash text not null references casts(hash),
score numeric,
begin_offset integer,
end_offset integer
);
create table if not exists likes(
type text not null,
fid int8 references profiles(id) not null,
cast_hash text references casts(hash) not null,
created_at timestamptz not null default now()
);
-- connects the farcaster profile fid to their ethereum mainnet wallet
create table if not exists verifications(
fid int8 not null,
verified_address text not null,
timestamp int8 not null
);
SELECT * from casts...