Work

Most onchain analysis starts with the assumption that someone has already decoded the dataset. On platforms like Dune, analysts can usually query human-readable tables without having to interpret the raw bytes recorded onchain. But what if the dataset you need doesn’t exist? I ran into this problem while trying to analyze liquidations on Jupiter Lend during the market selloff on October 10, 2025. I wanted to determine how many liquidations were processed and their total value. ...

In Part 1, we identified Jupiter Lend’s liquidation instructions, mapped their account arguments, and decoded the raw amounts of debt repaid and collateral seized. In this walkthrough, we’ll combine those findings into a single DuneSQL query using common table expressions (CTEs). The query reconstructs liquidation records from raw instructions and joins Dune’s prices.hour table to calculate the USD value of debt repaid and collateral seized. We’ll work through the query in four phases, following how each CTE transforms and passes data to the next. Select a CTE in the interactive query below to explore its role in building the final dataset. ...

Using an LLM for data analysis can sometimes create more problems than it solves. In a chat-based workflow, the analyst still has to supply table schemas, API documentation, and project-specific context while copying and pasting data between multiple interfaces and tools. If you’re not careful, this setup can increase token costs and introduce errors – undermining the time saved by using an LLM in the first place. At Blocmates, we created an agentic pipeline to automate these tasks. Instead of manually providing context and coordinating each step, the analyst fills out a data specification and sends it to Claude Code. The agent loads the necessary context, writes SQL queries, fetches data from APIs, and tests and publishes the queries to Dune. ...

With Crypto operating 24/7 it’s challenging to keep up with current news while also going deep on a report. Writing forces you to focus on a project or sector deeply, but you still have to track headlines that could change your thesis or reframe the data. To solve this, I built an AI research assistant to keep me up to date with the latest news in crypto and finances everyday, in around 20 minutes reading time – down from 1-2 hours. It runs on locally hosted models including OpenAI’s GPT OSS 20b, and Deepseek R1 via LM studio. ...