The flavor of SQL is Postgres SQL.

Basic queries

Each query should usually start with SELECT * from profiles or SELECT * from casts.

Useful SQL snippets

SQL Schema

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
);

Types of results

Casts

SELECT * from casts...