Pump Fun - Live Fetch

• Pump Fun - Live Fetch
  • Concept
    • Objective
  • Design
    • Initial Research

Concept

Objective

Practice utilizing Dune Analytics, Live Fetch functionality for interfacing with external data exposed through api(application programmable interfaces) endpoints. For this use-case, as data is retrieved as opposed to sent, the http_get method was employed.

Design

Pump.fun was selected for this instance due to relevance and possible extended use-case application.

Initial Research

Due to the lack of developer documentation, a cursory review of the Pump.fun frontend functionality was done using the Mullvad Browser and developer tools.

The Pump.fun frontend, displayed a variety of endpoints utilized by the website via the Network tab. When the instance was filtered for specifically XHR Requests as we are searching for potential data endpoints, it was observed an API instance for Pump.Fun was available:

https://frontend-api.pump.fun/coins?offset=0&limit=100&sort=last_trade_timestamp&order=DESC&includeNsfw=false

Parsed URI for reference:

Protocol:	https:
Hostname:	frontend-api.pump.fun
Path name:	/coins
Arguments:
	offset      = 0
	limit       = 100
	sort        = last_trade_timestamp
	order       = DESC
	includeNsfw = false

From this point, accessibility to the API was tested by pasting the endpoint into a browser url-bar. A valid response was returned, as such, said response was reviewed in greater depth. It was observed the response was composed of an array with nested JSON Objects, each object appeared to be of a consistent structure, so a singular object was copied for conversion into a generic structure, this structure is not necessarily needed, however this step undertaken to facillate implementing the API when composing the query utilized through Dune Analytics.

Sample of singular object copied available below:

{
  "mint": "34EKeXHKnp84a5B2xVkKY4STEchpMxfhDRsewN2Vpump",
  "name": "Weirdo",
  "symbol": "WRDO",
  "description": "Once We Hit 5,000 MC All Holders Will Get Free High Quality Merch No Matter How Many Tokens They Hold.",
  "image_uri": "https://cf-ipfs.com/ipfs/QmWfxthCWqEjCrzHaKriXcUZ2Q9qXHpJZfqAPFoeXxqJ8i",
  "metadata_uri": "https://cf-ipfs.com/ipfs/QmViCXHQPWvdTQznyBc2wquxAWvmjSpBGLVjDT9aQfMD4e",
  "twitter": null,
  "telegram": null,
  "bonding_curve": "B348rDzGw7Lg78NVyFzwCX7ndhyan4f9ghnURpspLj5T",
  "associated_bonding_curve": "E8Cee9q8BtZKMSLjYNYGFcwBLjUcckEexRBdFek19aD5",
  "creator": "BpBrVS5cSpxBUZV3kCt9LmXvdayfYVnssPTaSg1vGHcT",
  "created_timestamp": 1723001458578,
  "raydium_pool": null,
  "complete": false,
  "virtual_sol_reserves": 92779179223,
  "virtual_token_reserves": 346952931098009,
  "hidden": null,
  "total_supply": 1000000000000000,
  "website": "www.liusalnyc.com",
  "show_name": true,
  "last_trade_timestamp": 1723145694000,
  "king_of_the_hill_timestamp": 1723025638000,
  "market_cap": 267.411429352,
  "reply_count": 403,
  "last_reply": 1723145595199,
  "nsfw": false,
  "market_id": null,
  "inverted": null,
  "is_currently_live": false,
  "username": "BpBrVS",
  "profile_image": "https://cf-ipfs.com/ipfs/QmWfxthCWqEjCrzHaKriXcUZ2Q9qXHpJZfqAPFoeXxqJ8i",
  "usd_market_cap": 41788.384064837046
}

Generic typescript structure:

export interface Root {
  mint: string
  name: string
  symbol: string
  description: string
  image_uri: string
  metadata_uri: string
  twitter: any
  telegram: any
  bonding_curve: string
  associated_bonding_curve: string
  creator: string
  created_timestamp: number
  raydium_pool: any
  complete: boolean
  virtual_sol_reserves: number
  virtual_token_reserves: number
  hidden: any
  total_supply: number
  website: string
  show_name: boolean
  last_trade_timestamp: number
  king_of_the_hill_timestamp: number
  market_cap: number
  reply_count: number
  last_reply: number
  nsfw: boolean
  market_id: any
  inverted: any
  is_currently_live: boolean
  username: string
  profile_image: string
  usd_market_cap: number
}

The data was converted into a generic reference structure to ensure data type accuracy when the unit is referenced via Dune Analytics. In this particular case, a Typescript structure was employed as it was the most familiar(any other programming language does the same).

Dune Analytics query
https://dune.com/queries/3951402?sidebar=none

with data as (
    select
        json_parse(http_get('https://frontend-api.pump.fun/coins?offset=0&limit=100&sort=last_trade_timestamp&order=DESC&includeNsfw=false')) as response
),
unnested as (
    select
        json_extract(item, '$') as item
    from
        data,
        unnest(cast(json_extract(response, '$') as array(json))) as t(item)
),
extracted as (
    select
        json_extract_scalar(item, '$.mint') as mint,
        json_extract_scalar(item, '$.name') as name,
        json_extract_scalar(item, '$.symbol') as symbol,
        json_extract_scalar(item, '$.creator') as creator,
        from_unixtime(cast(json_extract_scalar(item, '$.created_timestamp') as bigint) / 1000) as created_timestamp,
        json_extract_scalar(item, '$.twitter') as twitter,
        json_extract_scalar(item, '$.telegram') as telegram,
        json_extract_scalar(item, '$.bonding_curve') as bonding_curve,
        json_extract_scalar(item, '$.associated_bonding_curve') as associated_bonding_curve,
        json_extract_scalar(item, '$.virtual_sol_reserves') as virtual_sol_reserves,
        json_extract_scalar(item, '$.virtual_token_reserves') as virtual_token_reserves,
        json_extract_scalar(item, '$.total_supply') as total_supply,
        json_extract_scalar(item, '$.website') as website,
        json_extract_scalar(item, '$.market_cap') as market_cap,
        json_extract_scalar(item, '$.is_currently_live') as is_currently_live,
        json_extract_scalar(item, '$.username') as username,
        json_extract_scalar(item, '$.usd_market_cap') as usd_market_cap
    from
        unnested
)
select *
from extracted
order by created_timestamp desc;