Skip to content

Instantly share code, notes, and snippets.

@myselfshravan

myselfshravan/golden_queries.yaml

Created March 2, 2026 12:25
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save myselfshravan/88ec4b87b8f0a9da31cc1ec7d1d1141e to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save myselfshravan/88ec4b87b8f0a9da31cc1ec7d1d1141e to your computer and use it in GitHub Desktop.
Download ZIP
Raw
golden_queries.yaml
# Klydo E-Commerce Golden Queries
# All amounts are in paisa (divide by 100 for INR)
# All date filters use IST (Asia/Kolkata) conversion
# All order queries exclude lifecycle='Draft' (cart stage)
version: "3.0"
last_updated: "2026-03-01"
queries:
# ===== ORDER QUERIES =====
order_summary_by_date:
name: "Order Summary by Date (IST)"
category: "orders"
description: "Full order summary for a given IST date - totals, breakdowns by lifecycle, line-item state, and payment mode"
type: "table"
sql: |
WITH ist_orders AS (
SELECT
o."entityId" as order_id,
o.lifecycle,
o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata' as ordered_ist
FROM "order" o
WHERE o.active = true
AND o.lifecycle != 'Draft'
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date = :target_date::date
),
line_data AS (
SELECT
io.order_id,
io.lifecycle,
ol.state,
ol."paymentMode",
ol."unitAmount" as amount_paisa,
COALESCE(ol."couponDiscount", 0) as coupon_paisa,
COALESCE(ol."clubMembershipDiscount", 0) as club_paisa,
COALESCE(ol."referralDiscount", 0) as referral_paisa,
COALESCE(ol."listingDiscountAmount", 0) as listing_discount_paisa
FROM ist_orders io
JOIN order_line ol ON io.order_id = ol."orderId" AND ol.active = true
)
SELECT * FROM (
SELECT
1 as section,
'TOTAL' as category,
'all_orders' as label,
COUNT(DISTINCT order_id) as order_count,
COUNT(*) as line_items,
COALESCE(ROUND(SUM(amount_paisa) / 100.0, 2), 0) as gross_revenue_inr,
COALESCE(ROUND(SUM(coupon_paisa + club_paisa + referral_paisa + listing_discount_paisa) / 100.0, 2), 0) as total_discounts_inr,
COALESCE(ROUND(SUM(amount_paisa - coupon_paisa - club_paisa - referral_paisa - listing_discount_paisa) / 100.0, 2), 0) as net_revenue_inr
FROM line_data
UNION ALL
SELECT
2 as section,
'LIFECYCLE' as category,
lifecycle as label,
COUNT(DISTINCT order_id) as order_count,
COUNT(*) as line_items,
COALESCE(ROUND(SUM(amount_paisa) / 100.0, 2), 0) as gross_revenue_inr,
COALESCE(ROUND(SUM(coupon_paisa + club_paisa + referral_paisa + listing_discount_paisa) / 100.0, 2), 0) as total_discounts_inr,
COALESCE(ROUND(SUM(amount_paisa - coupon_paisa - club_paisa - referral_paisa - listing_discount_paisa) / 100.0, 2), 0) as net_revenue_inr
FROM line_data
GROUP BY lifecycle
UNION ALL
SELECT
3 as section,
'LINE_STATE' as category,
state as label,
COUNT(DISTINCT order_id) as order_count,
COUNT(*) as line_items,
COALESCE(ROUND(SUM(amount_paisa) / 100.0, 2), 0) as gross_revenue_inr,
COALESCE(ROUND(SUM(coupon_paisa + club_paisa + referral_paisa + listing_discount_paisa) / 100.0, 2), 0) as total_discounts_inr,
COALESCE(ROUND(SUM(amount_paisa - coupon_paisa - club_paisa - referral_paisa - listing_discount_paisa) / 100.0, 2), 0) as net_revenue_inr
FROM line_data
GROUP BY state
UNION ALL
SELECT
4 as section,
'PAYMENT_MODE' as category,
"paymentMode" as label,
COUNT(DISTINCT order_id) as order_count,
COUNT(*) as line_items,
COALESCE(ROUND(SUM(amount_paisa) / 100.0, 2), 0) as gross_revenue_inr,
COALESCE(ROUND(SUM(coupon_paisa + club_paisa + referral_paisa + listing_discount_paisa) / 100.0, 2), 0) as total_discounts_inr,
COALESCE(ROUND(SUM(amount_paisa - coupon_paisa - club_paisa - referral_paisa - listing_discount_paisa) / 100.0, 2), 0) as net_revenue_inr
FROM line_data
GROUP BY "paymentMode"
) combined
ORDER BY section, order_count DESC
parameters:
target_date:
type: "string"
required: true
description: "Date in YYYY-MM-DD format (IST). e.g. 2026-02-22. Always pass an explicit date."
daily_orders:
name: "Daily Orders (IST)"
category: "orders"
description: "Order count and revenue per day in IST. Excludes Draft orders."
type: "table"
sql: |
SELECT
(o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date as date_ist,
COUNT(DISTINCT o."entityId") as order_count,
COALESCE(ROUND(SUM(ol."unitAmount") / 100.0, 2), 0) as gross_revenue_inr,
COUNT(ol.id) as line_items
FROM "order" o
JOIN order_line ol ON o."entityId" = ol."orderId" AND ol.active = true
WHERE o.active = true
AND o.lifecycle != 'Draft'
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
ORDER BY date_ist DESC
parameters:
days:
type: "integer"
default: 30
description: "Number of days to look back"
recent_orders:
name: "Recent Orders"
category: "orders"
description: "Latest orders with user and amount details. Excludes Draft orders."
type: "table"
sql: |
SELECT
o."entityId" as order_id,
(o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata') as ordered_ist,
o.lifecycle,
u.name as user_name,
u."mobileNumber",
COUNT(ol.id) as line_items,
ROUND(SUM(ol."unitAmount") / 100.0, 2) as total_inr,
ROUND(SUM(COALESCE(ol."couponDiscount", 0)) / 100.0, 2) as coupon_discount_inr
FROM "order" o
JOIN "user" u ON o."userId" = u."entityId"
JOIN order_line ol ON o."entityId" = ol."orderId" AND ol.active = true
WHERE o.active = true
AND o.lifecycle != 'Draft'
GROUP BY o."entityId", o."orderedAt", o.lifecycle, u.name, u."mobileNumber"
ORDER BY o."orderedAt" DESC
LIMIT :limit
parameters:
limit:
type: "integer"
default: 20
description: "Number of orders to return"
orders_by_lifecycle:
name: "Orders by Lifecycle (IST)"
category: "orders"
description: "Order distribution by lifecycle status in IST. Excludes Draft orders."
type: "table"
sql: |
SELECT
lifecycle,
COUNT(*) as order_count,
COALESCE(ROUND(SUM(ol.total_amount) / 100.0, 2), 0) as revenue_inr
FROM "order" o
LEFT JOIN (
SELECT "orderId", SUM("unitAmount") as total_amount
FROM order_line
WHERE active = true
GROUP BY "orderId"
) ol ON o."entityId" = ol."orderId"
WHERE o.active = true
AND o.lifecycle != 'Draft'
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY lifecycle
ORDER BY order_count DESC
parameters:
days:
type: "integer"
default: 30
description: "Number of days to look back"
order_detail:
name: "Order Detail"
category: "orders"
description: "Full details of a specific order - line items, products, prices, discounts"
type: "table"
sql: |
SELECT
ol."entityId" as line_id,
ol.state,
ol."paymentMode",
ps.title as product_title,
b.name as brand_name,
sku.size,
sku.color,
ROUND(ol."unitAmount" / 100.0, 2) as price_inr,
ROUND(COALESCE(ol."couponDiscount", 0) / 100.0, 2) as coupon_discount_inr,
ROUND(COALESCE(ol."clubMembershipDiscount", 0) / 100.0, 2) as club_discount_inr,
ROUND(COALESCE(ol."referralDiscount", 0) / 100.0, 2) as referral_discount_inr,
ROUND(COALESCE(ol."listingDiscountAmount", 0) / 100.0, 2) as listing_discount_inr
FROM order_line ol
JOIN stock_keeping_unit sku ON ol."skuId" = sku."entityId"
JOIN product_style ps ON sku."styleId" = ps."entityId"
LEFT JOIN brand b ON ps."brandId" = b."entityId"
WHERE ol."orderId" = :order_id
AND ol.active = true
parameters:
order_id:
type: "string"
required: true
description: "Order entityId"
orders_by_user:
name: "Orders by User"
category: "orders"
description: "All orders for a specific user - search by mobile number, email, or userId"
type: "table"
sql: |
SELECT
o."entityId" as order_id,
(o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata') as ordered_ist,
o.lifecycle,
COUNT(ol.id) as line_items,
ROUND(SUM(ol."unitAmount") / 100.0, 2) as total_inr,
STRING_AGG(DISTINCT ol.state, ', ') as line_states,
STRING_AGG(DISTINCT ol."paymentMode", ', ') as payment_modes
FROM "order" o
JOIN "user" u ON o."userId" = u."entityId"
JOIN order_line ol ON o."entityId" = ol."orderId" AND ol.active = true
WHERE o.active = true
AND o.lifecycle != 'Draft'
AND (u."mobileNumber" = :identifier OR u."entityId" = :identifier OR u.email = :identifier)
GROUP BY o."entityId", o."orderedAt", o.lifecycle
ORDER BY o."orderedAt" DESC
LIMIT :limit
parameters:
identifier:
type: "string"
required: true
description: "User mobile number, entityId, or email"
limit:
type: "integer"
default: 20
description: "Number of orders to return"
cancellation_analysis:
name: "Cancellation Analysis (IST)"
category: "orders"
description: "Cancelled order lines by type (seller vs customer) and brand"
type: "table"
sql: |
SELECT
ol.state as cancel_type,
b.name as brand,
COUNT(*) as cancelled_items,
ROUND(SUM(ol."unitAmount") / 100.0, 2) as lost_revenue_inr,
COUNT(DISTINCT ol."orderId") as affected_orders
FROM order_line ol
JOIN "order" o ON ol."orderId" = o."entityId"
JOIN stock_keeping_unit sku ON ol."skuId" = sku."entityId"
JOIN product_style ps ON sku."styleId" = ps."entityId"
LEFT JOIN brand b ON ps."brandId" = b."entityId"
WHERE ol.active = true AND o.active = true
AND ol.state IN ('SellerCancel', 'CustomerCancel')
AND o.lifecycle != 'Draft'
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY ol.state, b.name
ORDER BY cancelled_items DESC
parameters:
days:
type: "integer"
default: 30
description: "Time period in days"
# ===== REVENUE QUERIES =====
daily_summary:
name: "Daily Summary (IST)"
category: "revenue"
description: "Today's snapshot in IST - orders (excl. Draft), revenue, new users, carts"
type: "single"
sql: |
SELECT
(SELECT COUNT(*)
FROM "order"
WHERE active = true AND lifecycle != 'Draft'
AND ("orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date = (NOW() AT TIME ZONE 'Asia/Kolkata')::date
) as orders_today,
(SELECT COALESCE(ROUND(SUM(ol."unitAmount") / 100.0, 2), 0)
FROM order_line ol
JOIN "order" o ON ol."orderId" = o."entityId"
WHERE o.active = true AND ol.active = true AND o.lifecycle != 'Draft'
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date = (NOW() AT TIME ZONE 'Asia/Kolkata')::date
) as revenue_today_inr,
(SELECT COUNT(*)
FROM "user"
WHERE active = true
AND ("createdAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date = (NOW() AT TIME ZONE 'Asia/Kolkata')::date
) as new_users_today,
(SELECT COUNT(*)
FROM cart
WHERE active = true
AND ("createdAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date = (NOW() AT TIME ZONE 'Asia/Kolkata')::date
) as carts_today
weekly_revenue:
name: "Weekly Revenue (IST)"
category: "revenue"
description: "Revenue aggregated by week in IST. Excludes Draft orders."
type: "table"
sql: |
SELECT
DATE_TRUNC('week', o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date as week_ist,
COUNT(DISTINCT o."entityId") as orders,
ROUND(SUM(ol."unitAmount") / 100.0, 2) as revenue_inr,
COUNT(DISTINCT o."userId") as unique_customers
FROM "order" o
JOIN order_line ol ON o."entityId" = ol."orderId" AND ol.active = true
WHERE o.active = true
AND o.lifecycle != 'Draft'
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 week' * :weeks
GROUP BY DATE_TRUNC('week', o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
ORDER BY week_ist DESC
parameters:
weeks:
type: "integer"
default: 8
description: "Number of weeks"
aov_trend:
name: "AOV Trend (IST)"
category: "revenue"
description: "Average Order Value trending by day - gross AOV, net AOV, and items per order"
type: "table"
sql: |
SELECT
(o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date as date_ist,
COUNT(DISTINCT o."entityId") as orders,
ROUND(SUM(ol."unitAmount") / 100.0 / NULLIF(COUNT(DISTINCT o."entityId"), 0), 2) as gross_aov_inr,
ROUND(SUM(ol."unitAmount" - COALESCE(ol."couponDiscount", 0) - COALESCE(ol."clubMembershipDiscount", 0)
- COALESCE(ol."referralDiscount", 0) - COALESCE(ol."listingDiscountAmount", 0))
/ 100.0 / NULLIF(COUNT(DISTINCT o."entityId"), 0), 2) as net_aov_inr,
ROUND(COUNT(ol.id)::numeric / NULLIF(COUNT(DISTINCT o."entityId"), 0), 2) as avg_items_per_order
FROM "order" o
JOIN order_line ol ON o."entityId" = ol."orderId" AND ol.active = true
WHERE o.active = true AND o.lifecycle != 'Draft'
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
ORDER BY date_ist DESC
parameters:
days:
type: "integer"
default: 30
description: "Number of days to look back"
# ===== PRODUCT QUERIES =====
top_products:
name: "Top Selling Products"
category: "products"
description: "Best selling products by unit count. Excludes Draft orders."
type: "table"
sql: |
SELECT
ps.title,
b.name as brand,
COUNT(ol.id) as units_sold,
ROUND(SUM(ol."unitAmount") / 100.0, 2) as revenue_inr,
ROUND(AVG(ol."unitAmount") / 100.0, 2) as avg_price_inr
FROM order_line ol
JOIN stock_keeping_unit sku ON ol."skuId" = sku."entityId"
JOIN product_style ps ON sku."styleId" = ps."entityId"
LEFT JOIN brand b ON ps."brandId" = b."entityId"
JOIN "order" o ON ol."orderId" = o."entityId"
WHERE ol.active = true
AND o.active = true
AND o.lifecycle != 'Draft'
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY ps.title, b.name
ORDER BY units_sold DESC
LIMIT :limit
parameters:
limit:
type: "integer"
default: 20
description: "Number of products"
days:
type: "integer"
default: 30
description: "Time period in days"
low_stock:
name: "Low Stock Alert"
category: "products"
description: "Active listings with low inventory (above zero but below threshold)"
type: "table"
sql: |
SELECT
ps.title,
b.name as brand,
sku.size,
sku.color,
l.quantity as stock,
ROUND(l."sellingPrice" / 100.0, 2) as price_inr,
ROUND(l.mrp / 100.0, 2) as mrp_inr,
l."discountPercentage" as discount_pct
FROM listing l
JOIN stock_keeping_unit sku ON l."skuId" = sku."entityId"
JOIN product_style ps ON sku."styleId" = ps."entityId"
LEFT JOIN brand b ON ps."brandId" = b."entityId"
WHERE l.active = true
AND l.quantity <= :threshold
AND l.quantity > 0
ORDER BY l.quantity ASC
LIMIT :limit
parameters:
threshold:
type: "integer"
default: 5
description: "Stock threshold"
limit:
type: "integer"
default: 50
description: "Max results"
brand_performance:
name: "Brand Performance"
category: "products"
description: "Sales performance by brand. Excludes Draft orders."
type: "table"
sql: |
SELECT
b.name as brand,
COUNT(DISTINCT ol.id) as units_sold,
COUNT(DISTINCT o."entityId") as orders,
ROUND(SUM(ol."unitAmount") / 100.0, 2) as revenue_inr,
ROUND(AVG(ol."unitAmount") / 100.0, 2) as avg_price_inr
FROM order_line ol
JOIN stock_keeping_unit sku ON ol."skuId" = sku."entityId"
JOIN product_style ps ON sku."styleId" = ps."entityId"
JOIN brand b ON ps."brandId" = b."entityId"
JOIN "order" o ON ol."orderId" = o."entityId"
WHERE ol.active = true AND o.active = true
AND o.lifecycle != 'Draft'
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY b.name
ORDER BY revenue_inr DESC
LIMIT :limit
parameters:
days:
type: "integer"
default: 30
description: "Time period in days"
limit:
type: "integer"
default: 20
description: "Number of brands"
catalog_overview:
name: "Catalog Overview"
category: "products"
description: "Product catalog stats by leaf category - styles, SKUs, listings, price ranges"
type: "table"
sql: |
SELECT
c.name as category,
COUNT(DISTINCT ps.id) as styles,
COUNT(DISTINCT sku.id) as skus,
COUNT(DISTINCT l.id) as active_listings,
ROUND(AVG(l."sellingPrice") / 100.0, 2) as avg_price_inr,
ROUND(MIN(l."sellingPrice") / 100.0, 2) as min_price_inr,
ROUND(MAX(l."sellingPrice") / 100.0, 2) as max_price_inr
FROM category c
JOIN product_style ps ON ps."categoryId" = c."entityId" AND ps.active = true
JOIN stock_keeping_unit sku ON sku."styleId" = ps."entityId" AND sku.active = true
LEFT JOIN listing l ON l."skuId" = sku."entityId" AND l.active = true
WHERE c.active = true AND c."isLeaf" = true
GROUP BY c.name
ORDER BY styles DESC
inventory_health:
name: "Inventory Health"
category: "products"
description: "Stock health by category - out of stock, low stock, healthy, overstock counts and inventory value"
type: "table"
sql: |
SELECT
c.name as category,
COUNT(DISTINCT l."entityId") as total_listings,
COUNT(DISTINCT l."entityId") FILTER (WHERE l.quantity = 0) as out_of_stock,
COUNT(DISTINCT l."entityId") FILTER (WHERE l.quantity BETWEEN 1 AND 3) as low_stock,
COUNT(DISTINCT l."entityId") FILTER (WHERE l.quantity BETWEEN 4 AND 20) as healthy_stock,
COUNT(DISTINCT l."entityId") FILTER (WHERE l.quantity > 20) as overstock,
ROUND(AVG(l."discountPercentage")::numeric, 1) as avg_discount_pct,
ROUND(SUM(l.quantity * l."sellingPrice") / 100.0, 2) as total_inventory_value_inr
FROM listing l
JOIN stock_keeping_unit sku ON l."skuId" = sku."entityId" AND sku.active = true
JOIN product_style ps ON sku."styleId" = ps."entityId" AND ps.active = true
LEFT JOIN category c ON ps."categoryId" = c."entityId" AND c.active = true
WHERE l.active = true
GROUP BY c.name
ORDER BY total_listings DESC
# ===== USER QUERIES =====
top_customers:
name: "Top Customers"
category: "users"
description: "Highest spending customers. Excludes Draft orders."
type: "table"
sql: |
SELECT
u."entityId" as user_id,
u.name,
u.email,
COUNT(DISTINCT o."entityId") as total_orders,
ROUND(SUM(ol."unitAmount") / 100.0, 2) as total_spent_inr,
MIN(o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata') as first_order_ist,
MAX(o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata') as last_order_ist
FROM "user" u
JOIN "order" o ON u."entityId" = o."userId" AND o.active = true AND o.lifecycle != 'Draft'
JOIN order_line ol ON o."entityId" = ol."orderId" AND ol.active = true
WHERE u.active = true
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY u."entityId", u.name, u.email
ORDER BY total_spent_inr DESC
LIMIT :limit
parameters:
days:
type: "integer"
default: 90
description: "Time period in days"
limit:
type: "integer"
default: 20
description: "Number of customers"
user_growth:
name: "User Growth (IST)"
category: "users"
description: "New user signups per day in IST"
type: "table"
sql: |
SELECT
("createdAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date as date_ist,
COUNT(*) as new_users
FROM "user"
WHERE active = true
AND ("createdAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY ("createdAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
ORDER BY date_ist DESC
parameters:
days:
type: "integer"
default: 30
description: "Number of days"
repeat_customers:
name: "Repeat Customer Analysis (IST)"
category: "users"
description: "Breakdown of 1-time vs repeat buyers with revenue contribution"
type: "table"
sql: |
WITH customer_orders AS (
SELECT
o."userId",
COUNT(DISTINCT o."entityId") as order_count,
ROUND(SUM(ol."unitAmount") / 100.0, 2) as total_spent_inr
FROM "order" o
JOIN order_line ol ON o."entityId" = ol."orderId" AND ol.active = true
WHERE o.active = true AND o.lifecycle != 'Draft'
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY o."userId"
)
SELECT
CASE
WHEN order_count = 1 THEN '1 order'
WHEN order_count = 2 THEN '2 orders'
WHEN order_count BETWEEN 3 AND 5 THEN '3-5 orders'
ELSE '6+ orders'
END as customer_segment,
COUNT(*) as customers,
SUM(order_count) as total_orders,
ROUND(SUM(total_spent_inr), 2) as total_revenue_inr,
ROUND(AVG(total_spent_inr), 2) as avg_ltv_inr
FROM customer_orders
GROUP BY
CASE
WHEN order_count = 1 THEN '1 order'
WHEN order_count = 2 THEN '2 orders'
WHEN order_count BETWEEN 3 AND 5 THEN '3-5 orders'
ELSE '6+ orders'
END
ORDER BY MIN(order_count)
parameters:
days:
type: "integer"
default: 180
description: "Time period in days to analyze"
# ===== PAYMENT QUERIES =====
payment_summary:
name: "Payment Summary (IST)"
category: "payments"
description: "Payments by provider (Rzp, JioOne) and state (Created, Approved, Error)"
type: "table"
sql: |
SELECT
"paymentProvider",
"paymentState",
COUNT(*) as payment_count,
ROUND(SUM("amountPaisa") / 100.0, 2) as total_inr
FROM customer_payment
WHERE active = true
AND ("createdAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY "paymentProvider", "paymentState"
ORDER BY total_inr DESC
parameters:
days:
type: "integer"
default: 30
description: "Time period in days"
payment_mode_trend:
name: "Payment Mode Trend (IST)"
category: "payments"
description: "Prepaid/TNPL/POD distribution trending by week with percentage share"
type: "table"
sql: |
SELECT
DATE_TRUNC('week', o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date as week_ist,
ol."paymentMode",
COUNT(DISTINCT o."entityId") as orders,
ROUND(SUM(ol."unitAmount") / 100.0, 2) as revenue_inr,
ROUND(100.0 * COUNT(DISTINCT o."entityId") / NULLIF(SUM(COUNT(DISTINCT o."entityId")) OVER (
PARTITION BY DATE_TRUNC('week', o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
), 0), 1) as pct_of_orders
FROM "order" o
JOIN order_line ol ON o."entityId" = ol."orderId" AND ol.active = true
WHERE o.active = true AND o.lifecycle != 'Draft'
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 week' * :weeks
GROUP BY DATE_TRUNC('week', o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date, ol."paymentMode"
ORDER BY week_ist DESC, orders DESC
parameters:
weeks:
type: "integer"
default: 8
description: "Number of weeks"
refund_summary:
name: "Refund Summary (IST)"
category: "payments"
description: "Customer payment refunds by type (Instant, Normal) and status"
type: "table"
sql: |
SELECT
cpr.type as refund_type,
cpr.status as refund_status,
COUNT(*) as refund_count,
ROUND(SUM(cpr."amountPaisa") / 100.0, 2) as total_refund_inr,
ROUND(AVG(cpr."amountPaisa") / 100.0, 2) as avg_refund_inr
FROM customer_payment_refund cpr
WHERE cpr.active = true
AND (cpr."createdAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY cpr.type, cpr.status
ORDER BY total_refund_inr DESC
parameters:
days:
type: "integer"
default: 30
description: "Time period in days"
# ===== RETURN QUERIES =====
return_overview:
name: "Return Overview (IST)"
category: "returns"
description: "Return requests by status (Draft, Created, PickUpInitiated, Returned, Cancelled)"
type: "table"
sql: |
SELECT
rr.status,
COUNT(*) as return_count,
COUNT(DISTINCT rr."userId") as unique_users
FROM return_request rr
WHERE rr.active = true
AND (rr."createdAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY rr.status
ORDER BY return_count DESC
parameters:
days:
type: "integer"
default: 30
description: "Time period in days"
return_reasons:
name: "Return Reasons (IST)"
category: "returns"
description: "Return request items grouped by reason category and detail"
type: "table"
sql: |
SELECT
rr_reason.l1 as reason_category,
rr_reason.l2 as reason_detail,
COUNT(*) as return_items,
COUNT(DISTINCT rri."returnRequestId") as return_requests,
COUNT(DISTINCT rr."userId") as unique_users
FROM return_request_item rri
JOIN return_request rr ON rri."returnRequestId" = rr."entityId" AND rr.active = true
LEFT JOIN return_reason rr_reason ON rri."reasonId" = rr_reason."entityId" AND rr_reason.active = true
WHERE rri.active = true
AND (rr."createdAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY rr_reason.l1, rr_reason.l2
ORDER BY return_items DESC
parameters:
days:
type: "integer"
default: 30
description: "Time period in days"
exchange_overview:
name: "Exchange Request Overview (IST)"
category: "returns"
description: "Exchange requests by status (Draft, Created, Approved, Cancelled)"
type: "table"
sql: |
SELECT
er.status,
COUNT(*) as exchange_count,
COUNT(DISTINCT er."userId") as unique_users,
ROUND(SUM(COALESCE(er."exchangeDiscount", 0)) / 100.0, 2) as total_exchange_discount_inr,
COUNT(DISTINCT er."exchangeOrderId") FILTER (WHERE er."exchangeOrderId" IS NOT NULL) as completed_exchanges
FROM exchange_request er
WHERE er.active = true
AND (er."createdAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY er.status
ORDER BY exchange_count DESC
parameters:
days:
type: "integer"
default: 30
description: "Time period in days"
rto_analysis:
name: "RTO Analysis (IST)"
category: "returns"
description: "Return to Origin - failed deliveries by payment mode and brand"
type: "table"
sql: |
SELECT
ol."paymentMode",
b.name as brand,
COUNT(*) as rto_items,
ROUND(SUM(ol."unitAmount") / 100.0, 2) as rto_value_inr,
COUNT(DISTINCT ol."orderId") as rto_orders
FROM order_line ol
JOIN "order" o ON ol."orderId" = o."entityId"
JOIN stock_keeping_unit sku ON ol."skuId" = sku."entityId"
JOIN product_style ps ON sku."styleId" = ps."entityId"
LEFT JOIN brand b ON ps."brandId" = b."entityId"
WHERE ol.active = true AND o.active = true
AND ol.state = 'ReturnedToOrigin'
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY ol."paymentMode", b.name
ORDER BY rto_items DESC
parameters:
days:
type: "integer"
default: 30
description: "Time period in days"
# ===== COUPON / MARKETING QUERIES =====
coupon_usage:
name: "Coupon Usage (IST)"
category: "marketing"
description: "Active coupons and their usage count. Excludes Draft orders."
type: "table"
sql: |
SELECT
c.code,
c.type,
c."discountBps",
ROUND(COALESCE(c."maxDiscountAmount", 0) / 100.0, 2) as max_discount_inr,
c."startDate",
c."validTill",
COUNT(o.id) as times_used
FROM coupon c
LEFT JOIN "order" o ON o."couponId" = c."entityId" AND o.active = true AND o.lifecycle != 'Draft'
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
WHERE c.active = true
GROUP BY c.code, c.type, c."discountBps", c."maxDiscountAmount", c."startDate", c."validTill"
ORDER BY times_used DESC
LIMIT :limit
parameters:
days:
type: "integer"
default: 90
description: "Time period in days"
limit:
type: "integer"
default: 20
description: "Number of coupons"
coupon_effectiveness:
name: "Coupon Effectiveness (IST)"
category: "marketing"
description: "Coupon ROI - usage count, revenue driven, discount given, net revenue"
type: "table"
sql: |
SELECT
c.code,
c.type as coupon_type,
ROUND(c."discountBps" / 100.0, 1) as discount_pct,
ROUND(COALESCE(c."maxDiscountAmount", 0) / 100.0, 2) as max_discount_inr,
COUNT(DISTINCT o."entityId") as orders_with_coupon,
ROUND(SUM(ol."unitAmount") / 100.0, 2) as gross_revenue_inr,
ROUND(SUM(COALESCE(ol."couponDiscount", 0)) / 100.0, 2) as total_coupon_discount_inr,
ROUND(AVG(COALESCE(ol."couponDiscount", 0)) / 100.0, 2) as avg_discount_per_line_inr,
ROUND(SUM(ol."unitAmount" - COALESCE(ol."couponDiscount", 0)) / 100.0, 2) as net_revenue_after_coupon_inr
FROM coupon c
JOIN "order" o ON o."couponId" = c."entityId" AND o.active = true AND o.lifecycle != 'Draft'
JOIN order_line ol ON o."entityId" = ol."orderId" AND ol.active = true
WHERE c.active = true
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY c.code, c.type, c."discountBps", c."maxDiscountAmount"
ORDER BY orders_with_coupon DESC
LIMIT :limit
parameters:
days:
type: "integer"
default: 30
description: "Time period in days"
limit:
type: "integer"
default: 20
description: "Number of coupons"
# ===== LOGISTICS QUERIES =====
shipment_status:
name: "Shipment Status (IST)"
category: "logistics"
description: "Shipments by current status with overdue count"
type: "table"
sql: |
SELECT
status,
COUNT(*) as shipment_count,
COUNT(CASE WHEN "promisedEta" < NOW() AND status NOT IN ('Delivered', 'Cancelled') THEN 1 END) as overdue
FROM shipment
WHERE active = true
AND ("createdAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY status
ORDER BY shipment_count DESC
parameters:
days:
type: "integer"
default: 30
description: "Time period in days"
delivery_performance:
name: "Delivery Performance (IST)"
category: "logistics"
description: "Shipment delivery performance - status distribution and overdue tracking"
type: "table"
sql: |
SELECT
s.status,
COUNT(*) as shipment_count,
COUNT(CASE WHEN s."promisedEta" IS NOT NULL AND s."promisedEta" < NOW()
AND s.status NOT IN ('Delivered', 'Cancelled', 'RtoDelivered') THEN 1 END) as overdue,
ROUND(AVG(
EXTRACT(EPOCH FROM (LEAST(s."updatedAt", NOW()) - s."createdAt")) / 3600.0
), 1) as avg_age_hours
FROM shipment s
WHERE s.active = true
AND (s."createdAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY s.status
ORDER BY shipment_count DESC
parameters:
days:
type: "integer"
default: 30
description: "Time period in days"
city_wise_orders:
name: "City-wise Orders (IST)"
category: "logistics"
description: "Order distribution by city and state for geographic analysis"
type: "table"
sql: |
SELECT
ua.state,
ua.city,
COUNT(DISTINCT o."entityId") as orders,
ROUND(SUM(ol."unitAmount") / 100.0, 2) as revenue_inr,
COUNT(DISTINCT o."userId") as unique_customers
FROM "order" o
JOIN user_address ua ON o."userAddressId" = ua."entityId"
JOIN order_line ol ON o."entityId" = ol."orderId" AND ol.active = true
WHERE o.active = true AND o.lifecycle != 'Draft'
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY ua.state, ua.city
ORDER BY orders DESC
LIMIT :limit
parameters:
days:
type: "integer"
default: 30
description: "Time period in days"
limit:
type: "integer"
default: 30
description: "Number of cities to return"
# ===== WALLET QUERIES =====
wallet_stats:
name: "Wallet Stats"
category: "wallet"
description: "Wallet balance distribution - total available, credited, used"
type: "table"
sql: |
SELECT
COUNT(*) as total_wallets,
ROUND(SUM("availableBalancePaisa") / 100.0, 2) as total_available_inr,
ROUND(AVG("availableBalancePaisa") / 100.0, 2) as avg_balance_inr,
ROUND(SUM("lifetimeCreditedPaisa") / 100.0, 2) as total_credited_inr,
ROUND(SUM("lifetimeUsedInOrdersPaisa") / 100.0, 2) as total_used_inr
FROM wallet
WHERE active = true
# ===== CART QUERIES =====
cart_analytics:
name: "Cart Analytics (IST)"
category: "orders"
description: "Active cart items with product details and estimated value"
type: "table"
sql: |
SELECT
c.type as cart_type,
COUNT(DISTINCT c."userId") as unique_users,
COUNT(*) as cart_items,
SUM(c.quantity) as total_units,
ROUND(SUM(l."sellingPrice" * c.quantity) / 100.0, 2) as cart_value_inr
FROM cart c
JOIN listing l ON c."skuId" = l."skuId" AND l.active = true
WHERE c.active = true
AND (c."createdAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY c.type
ORDER BY cart_value_inr DESC
parameters:
days:
type: "integer"
default: 7
description: "Number of days to look back"
# ===== TNPL (TRY NOW PAY LATER) QUERIES =====
tnpl_conversion_funnel:
name: "TNPL Conversion Funnel (IST)"
category: "tnpl"
description: "TNPL orders by payment conversion status - Paid, Pending, Error, No Payment (failure). Excludes internal test users. Uses lifecycle=Completed for clean data."
type: "table"
sql: |
SELECT
CASE
WHEN cp."paymentState" = 'Approved' THEN 'Converted & Paid'
WHEN cp."paymentState" = 'Created' THEN 'Pending Payment'
WHEN cp."paymentState" = 'Error' THEN 'Payment Error'
WHEN cp."paymentState" IS NULL THEN 'No Payment (TNPL Failure)'
END as tnpl_status,
COUNT(DISTINCT o."entityId") as order_count,
ROUND(SUM(ol."unitAmount") / 100.0, 2) as revenue_inr,
ROUND(AVG(ol."unitAmount") / 100.0, 2) as avg_order_value_inr
FROM "order" o
JOIN order_line ol ON o."entityId" = ol."orderId" AND ol.active = true
LEFT JOIN customer_payment cp ON cp."orderId" = o."entityId" AND cp.active = true
JOIN "user" u ON o."userId" = u."entityId"
WHERE ol."paymentMode" = 'TNPL'
AND o.lifecycle = 'Completed'
AND o.active = true
AND u."mobileNumber" NOT IN ('+91-9945332995', '+91-9945050407', '+91-9570852682', '+91-64832322015', '+91-7976884108', '+91-196364482684', '+91-6361686430')
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY tnpl_status
ORDER BY order_count DESC
parameters:
days:
type: "integer"
default: 30
description: "Time period in days"
tnpl_daily_trend:
name: "TNPL Daily Conversion Trend (IST)"
category: "tnpl"
description: "Daily TNPL orders with conversion rate (paid vs total). Excludes internal test users."
type: "table"
sql: |
SELECT
(o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date as date_ist,
COUNT(DISTINCT o."entityId") as total_tnpl_orders,
COUNT(DISTINCT CASE WHEN cp."paymentState" = 'Approved' THEN o."entityId" END) as converted_orders,
ROUND(
(COUNT(DISTINCT CASE WHEN cp."paymentState" = 'Approved' THEN o."entityId" END)::numeric
/ NULLIF(COUNT(DISTINCT o."entityId"), 0)) * 100, 1
) as conversion_rate_pct
FROM "order" o
JOIN order_line ol ON o."entityId" = ol."orderId" AND ol.active = true
LEFT JOIN customer_payment cp ON cp."orderId" = o."entityId" AND cp.active = true
JOIN "user" u ON o."userId" = u."entityId"
WHERE ol."paymentMode" = 'TNPL'
AND o.lifecycle != 'Draft'
AND o.active = true
AND u."mobileNumber" NOT IN ('+91-9945332995', '+91-9945050407', '+91-9570852682', '+91-64832322015', '+91-7976884108', '+91-196364482684', '+91-6361686430')
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
ORDER BY date_ist DESC
parameters:
days:
type: "integer"
default: 30
description: "Time period in days"
tnpl_payment_sla:
name: "TNPL Payment SLA (IST)"
category: "tnpl"
description: "Average hours from TNPL delivery to payment approval, using tnpl_delivery_log"
type: "single"
sql: |
SELECT
ROUND(AVG(EXTRACT(EPOCH FROM (cp."paymentApprovedOn" - tdl."deliveredAt")) / 3600.0), 1) as avg_hours_to_payment,
ROUND(MIN(EXTRACT(EPOCH FROM (cp."paymentApprovedOn" - tdl."deliveredAt")) / 3600.0), 1) as min_hours,
ROUND(MAX(EXTRACT(EPOCH FROM (cp."paymentApprovedOn" - tdl."deliveredAt")) / 3600.0), 1) as max_hours,
COUNT(*) as paid_orders
FROM tnpl_delivery_log tdl
JOIN customer_payment cp ON tdl."orderId" = cp."orderId" AND cp.active = true
WHERE tdl.active = true
AND cp."paymentState" = 'Approved'
AND (tdl."deliveredAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
parameters:
days:
type: "integer"
default: 30
description: "Time period in days"
tnpl_abuser_detection:
name: "TNPL Abuser Detection (IST)"
category: "tnpl"
description: "Users with high TNPL order count and low payment rate — potential abusers. Excludes internal test users."
type: "table"
sql: |
SELECT
u."mobileNumber",
u.name,
COUNT(DISTINCT o."entityId") as total_tnpl_orders,
COUNT(DISTINCT CASE WHEN cp."paymentState" = 'Approved' THEN o."entityId" END) as paid_orders,
COUNT(DISTINCT CASE WHEN cp."paymentState" IS NULL THEN o."entityId" END) as unpaid_orders,
ROUND(
(COUNT(DISTINCT CASE WHEN cp."paymentState" = 'Approved' THEN o."entityId" END)::numeric
/ NULLIF(COUNT(DISTINCT o."entityId"), 0)) * 100, 1
) as payment_rate_pct
FROM "order" o
JOIN order_line ol ON o."entityId" = ol."orderId" AND ol.active = true
LEFT JOIN customer_payment cp ON cp."orderId" = o."entityId" AND cp.active = true
JOIN "user" u ON o."userId" = u."entityId"
WHERE ol."paymentMode" = 'TNPL'
AND o.lifecycle != 'Draft'
AND o.active = true
AND u."mobileNumber" NOT IN ('+91-9945332995', '+91-9945050407', '+91-9570852682', '+91-64832322015', '+91-7976884108', '+91-196364482684', '+91-6361686430')
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY u."mobileNumber", u.name
HAVING COUNT(DISTINCT o."entityId") >= :min_orders
ORDER BY total_tnpl_orders DESC
LIMIT :limit
parameters:
days:
type: "integer"
default: 30
description: "Time period in days"
min_orders:
type: "integer"
default: 3
description: "Minimum TNPL orders to flag"
limit:
type: "integer"
default: 30
description: "Number of users to return"
tnpl_rto_breakdown:
name: "TNPL RTO Breakdown (IST)"
category: "tnpl"
description: "TNPL ReturnedToOrigin orders split by paid (normal return) vs unpaid (TNPL failure + RTO)"
type: "table"
sql: |
SELECT
CASE
WHEN cp."paymentState" = 'Approved' THEN 'Paid (Normal Return)'
WHEN cp."paymentState" IS NULL THEN 'Unpaid (TNPL Failure + RTO)'
ELSE 'Other'
END as rto_type,
COUNT(DISTINCT o."entityId") as order_count,
ROUND(SUM(ol."unitAmount") / 100.0, 2) as value_inr
FROM "order" o
JOIN order_line ol ON o."entityId" = ol."orderId" AND ol.active = true
LEFT JOIN customer_payment cp ON cp."orderId" = o."entityId" AND cp.active = true
WHERE ol."paymentMode" = 'TNPL'
AND ol.state = 'ReturnedToOrigin'
AND o.active = true
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY rto_type
ORDER BY order_count DESC
parameters:
days:
type: "integer"
default: 30
description: "Time period in days"
tnpl_aov_by_conversion:
name: "TNPL AOV by Conversion (IST)"
category: "tnpl"
description: "Average order value comparison between paid and unpaid TNPL orders"
type: "table"
sql: |
SELECT
CASE
WHEN cp."paymentState" = 'Approved' THEN 'Paid'
WHEN cp."paymentState" IS NULL THEN 'Unpaid'
ELSE 'Other'
END as payment_status,
COUNT(DISTINCT o."entityId") as order_count,
ROUND(AVG(ol."unitAmount") / 100.0, 2) as avg_line_value_inr,
ROUND(SUM(ol."unitAmount") / 100.0, 2) as total_revenue_inr
FROM "order" o
JOIN order_line ol ON o."entityId" = ol."orderId" AND ol.active = true
LEFT JOIN customer_payment cp ON cp."orderId" = o."entityId" AND cp.active = true
WHERE ol."paymentMode" = 'TNPL'
AND o.lifecycle != 'Draft'
AND o.active = true
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY payment_status
ORDER BY order_count DESC
parameters:
days:
type: "integer"
default: 30
description: "Time period in days"
# ===== ADDITIONAL QUERIES FROM LOG PATTERNS =====
hourly_orders:
name: "Hourly Order Distribution (IST)"
category: "orders"
description: "Order count by hour of day in IST for a given date — useful for peak hours analysis"
type: "table"
sql: |
SELECT
EXTRACT(HOUR FROM o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::int as hour_ist,
COUNT(DISTINCT o."entityId") as order_count,
ROUND(SUM(ol."unitAmount") / 100.0, 2) as revenue_inr
FROM "order" o
JOIN order_line ol ON o."entityId" = ol."orderId" AND ol.active = true
WHERE o.active = true
AND o.lifecycle != 'Draft'
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date = :target_date::date
GROUP BY EXTRACT(HOUR FROM o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')
ORDER BY hour_ist
parameters:
target_date:
type: "string"
required: true
description: "Date in YYYY-MM-DD format (IST)"
user_wallet_top:
name: "Top Wallet Balances"
category: "wallet"
description: "Top users by wallet balance with name and phone number"
type: "table"
sql: |
SELECT
u."entityId" as user_id,
u.name,
u."mobileNumber",
ROUND(w."availableBalancePaisa" / 100.0, 2) as available_balance_inr,
ROUND(w."pendingBalancePaisa" / 100.0, 2) as pending_balance_inr,
ROUND(w."lifetimeCreditedPaisa" / 100.0, 2) as lifetime_credited_inr,
ROUND(w."lifetimeUsedInOrdersPaisa" / 100.0, 2) as lifetime_used_inr
FROM wallet w
JOIN "user" u ON w."userId" = u."entityId"
WHERE w.active = true AND u.active = true
ORDER BY w."availableBalancePaisa" DESC
LIMIT :limit
parameters:
limit:
type: "integer"
default: 20
description: "Number of users to return"
user_profile:
name: "User Profile"
category: "users"
description: "Single user lookup with profile, wallet balance, and order count. Search by phone, email, or userId."
type: "single"
sql: |
SELECT
u."entityId" as user_id,
u.name,
u."mobileNumber",
u.email,
u.dob,
(u."createdAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata') as registered_ist,
ROUND(COALESCE(w."availableBalancePaisa", 0) / 100.0, 2) as wallet_balance_inr,
(SELECT COUNT(*) FROM "order" o WHERE o."userId" = u."entityId" AND o.active = true AND o.lifecycle != 'Draft') as total_orders,
(SELECT MAX(o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata') FROM "order" o WHERE o."userId" = u."entityId" AND o.active = true AND o.lifecycle != 'Draft') as last_order_ist
FROM "user" u
LEFT JOIN wallet w ON w."userId" = u."entityId" AND w.active = true
WHERE u.active = true
AND (u."mobileNumber" = :identifier OR u."entityId" = :identifier OR u.email = :identifier)
parameters:
identifier:
type: "string"
required: true
description: "User mobile number, entityId, or email"
weekly_order_trend:
name: "Daily Order Trend (IST)"
category: "orders"
description: "Daily order count trend for the last N days — simple trend line"
type: "table"
sql: |
SELECT
(o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date as date_ist,
COUNT(DISTINCT o."entityId") as order_count
FROM "order" o
WHERE o.active = true
AND o.lifecycle != 'Draft'
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
ORDER BY date_ist
parameters:
days:
type: "integer"
default: 7
description: "Number of days to look back"
category_sales:
name: "Category Sales (IST)"
category: "products"
description: "Sales by product category using product_style.categoryId join. Excludes Draft orders."
type: "table"
sql: |
SELECT
c.name as category,
COUNT(DISTINCT ol."entityId") as items_sold,
COUNT(DISTINCT o."entityId") as orders,
ROUND(SUM(ol."unitAmount") / 100.0, 2) as revenue_inr,
ROUND(AVG(ol."unitAmount") / 100.0, 2) as avg_price_inr
FROM order_line ol
JOIN "order" o ON ol."orderId" = o."entityId" AND o.active = true
JOIN stock_keeping_unit sku ON ol."skuId" = sku."entityId" AND sku.active = true
JOIN product_style ps ON sku."styleId" = ps."entityId" AND ps.active = true
LEFT JOIN category c ON ps."categoryId" = c."entityId" AND c.active = true
WHERE ol.active = true
AND o.lifecycle != 'Draft'
AND (o."orderedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata')::date
>= (NOW() AT TIME ZONE 'Asia/Kolkata')::date - INTERVAL '1 day' * :days
GROUP BY c.name
ORDER BY revenue_inr DESC
LIMIT :limit
parameters:
days:
type: "integer"
default: 30
description: "Time period in days"
limit:
type: "integer"
default: 20
description: "Number of categories to return"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment