# Pump Fun - Live Fetch
[TOC]
## Concept
### Objective
> Practice utilizing Dune Analytics, [Live Fetch](https://docs.dune.com/query-engine/Functions-and-operators/live-fetch) functionality for interfacing with external data exposed through [api(application programmable interfaces)](https://developer.mozilla.org/en-US/docs/Learn/JavaScript/Client-side_web_APIs/Introduction) 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](https://developer.mozilla.org/en-US/docs/Web/API/XMLHttpRequest) 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:
```json=
{
"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:
```typescript=
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
```sql=
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;
```
Sign in with Wallet
Connect another wallet