Weld logo

Weld SQL templates

Get inspiration on how to model your data, with our collection of foundational SQL templates we have battle-tested at Weld. All the templates will work with data from the Weld Data Integrations.

shopify

Shopify

shopify
Customer growth by month
Analyzes the growth of customers on a monthly basis using data from the Shopify integration. It selects the month and counts the number of new customers, grouping the results by month. The output is ordered by month.
shopify
Monthly sales
Retrieves monthly sales data from a Shopify integration by joining order and order line tables and grouping the results by month. The template uses the date_trunc function to extract the month from the created_at date field and calculates the total sales by multiplying the quantity and price fields.
shopify
Top ten products
Retrieves the top ten products based on the quantity sold. It uses an inner join to combine order lines and orders data and groups the results by product title. The template is limited to only display the top ten products based on the count of orders.
shopify
Yealy revenue
Retrieve the total sales revenue for each year by joining the order and order line tables and grouping the results by year. The template uses the date_trunc function to extract the year from the created_at column.
shopify
Orders with tax, shipping , refunds and discounts
Retrieves detailed information about orders, including tax, shipping, refunds, and discounts, from a Shopify integration. It uses several subqueries to calculate various metrics such as total order line discounts, order level discounts, and line return.
shopify
Customer cohort
Generates a customer cohort report for a Shopify integration. The report includes information on customer revenue, cohort, and customer retention over time.
hubspot

Hubspot

hubspot
New companies by month
Retrieves a list of new companies created each month from the Hubspot integration and groups them by month. The results are ordered by month.
hubspot
Closed won deals by owner
Retrieves the number of closed won deals by owner from Hubspot and orders them in descending order based on the number of deals.
hubspot
Closed lost deals by owner
Retrieves the number of closed lost deals by owner from Hubspot and orders them by the highest number of deals. It uses a left join to match the owner ID from the deals table to the owners table and concatenates the first and last name of the owner for readability.
hubspot
Closed won deals amount by owner
Retrieves the total amount of closed won deals for each owner in HubSpot and orders them in descending order.
hubspot
Emails by owner
Retrieves the number of emails sent by each owner in HubSpot by joining the engagements and owners tables and grouping the results by owner.
hubspot
New deals by month
Retrieves the number of new deals created each month from Hubspot and organizes them by month.
hubspot
Calls by owner
Retrieves the number of calls made by each owner in HubSpot and displays it alongside their first and last name. It uses a left join to connect the engagements and owners tables and filters the results to only include calls. The output is grouped by owner.
hubspot
Meetings by owner
Retrieves the number of meetings organized by each owner in Hubspot and displays it alongside their first and last name. It uses a left join to connect the engagements and owners tables and filters the results to only include meetings. The output is grouped by owner.
hubspot
Tasks completed by owner
Retrieves the number of completed tasks for each owner in Hubspot, using a left join between the engagements and owners tables. The result includes the owner's first and last name, as well as the count of completed tasks.
hubspot
Deals by deal stage
Retrieves the number of deals in each stage of the sales pipeline from HubSpot and groups them by their respective stage labels.
hubspot
Conversion from contacts to deals by owner
Converts contacts to deals by owner in Hubspot, displaying the owner's name, the number of contacts they have, the number of deals they have, and the conversion rate between the two. It uses left joins to connect the necessary tables and groups the results by owner.
hubspot
New contacts by month
Retrieves the number of new contacts added to Hubspot each month and groups them by month. It uses the date_trunc function to extract the month from the createdAt field and orders the results by month.
intercom

Intercom

intercom
Monthly Conversations By Team
Extracts monthly conversation data by team from the Intercom integration. It selects the month, team ID, team name, and ticket count from the conversation table. It then joins the conversation table with the team table and groups the results by month, team ID, and team name, ordering them by month and ticket count in descending order.
intercom
Monthly Tickets By Team
Extracts monthly ticket data for each team, including the team ID, team name, and the count of tickets. It uses the Intercom integration and joins the ticket and team tables based on the team assignee ID. The results are grouped by month, team ID, and team name, and ordered by month and ticket count in descending order.
intercom
Monthly Ticket Resolution Time
Extracts the monthly ticket resolution time for a specific integration (intercom). It selects the month, team ID, team name, and average resolution time in days. It joins the ticket and team tables, filters for resolved tickets, groups the results by month and team, and sorts them in descending order by month and ascending order by average resolution time.
intercom
Monthly Tags By Conversation
This SQL template extracts monthly tags by conversation from the Intercom integration. It uses a common table expression (CTE) to join the conversation and conversation_tag tables, and then retrieves the month, tag name, and tag count for each conversation. The results are grouped by month and tag name, and ordered by month in descending order.
intercom
Monthly Average Conversations Rating
Extracts the monthly average rating for conversations from the Intercom integration. It selects the truncated month and calculates the average rating for conversations that have a non-null rating value. The results are grouped by month and ordered in descending order.
intercom
Count of Tickets By State
Extracts the count of tickets grouped by month and ticket state from the Intercom integration. It uses the `date_trunc` function to truncate the `created_at` column to the month level. The result is then ordered by month in descending order, followed by the ticket count in descending order.
amazon-mws

Amazon Selling Partner API

amazon-mws
Monthly Sales Performance Reports
Extracts monthly sales performance reports for Amazon integration. It calculates the total sales amount, number of orders, and total items sold for each month. Additionally, it calculates the average order value by dividing the total sales amount by the number of orders. The results are ordered in descending order by month.
amazon-mws
Monthly Inventory Reports
Extracts monthly inventory reports for Amazon integration. It retrieves data from the Amazon Selling API to calculate various metrics such as total items listed, total fulfillable quantity, total damaged items, expired items, total unfulfillable quantity, items in transit, and items receiving. The results are ordered by month in descending order.
amazon-mws
Monthly Financial Analysis
Generates a monthly financial analysis for Amazon integration. It calculates the gross revenue, refunds, net revenue, average item price, average promotion discount, average shipping price, and average shipping tax. The analysis is based on data from the settlement report and orders by last updated date report. The results are sorted in descending order by month.
amazon-mws
Monthly Shipping and Delivery Reports
Generates a monthly shipping and delivery report for Amazon integration. It calculates various metrics such as the number of orders, total items shipped and unshipped, average days to ship, and the count of premium and regular orders. It also includes information on different shipping service levels and the number of orders for each level. The final result is sorted by month and shipping service level.
amazon-mws
Monthly Returns Analysis
Extracts a monthly returns analysis for Amazon integration. It calculates various metrics such as total returns, total refunded amount, in-policy returns, out-of-policy returns, prime returns, and non-prime returns. Additionally, it provides information on return reasons and their respective counts. The results are sorted by month and reason count in descending order.
amazon-mws
Monthly Order Update Tracking Report
Extracts a monthly order update tracking report for Amazon integration. It retrieves data from the "orders_by_last_updated_date_report" table and calculates various metrics such as total orders updated, total updates, average price updated, status breakdown, and channel breakdown. The results are sorted by month, status count, and channel count in descending order.
klaviyo

Klaviyo

klaviyo
Monthly User Engagement per Campaign
Calculates the monthly user engagement per campaign using data from Klaviyo integration. It extracts the year, month, campaign ID, event name, and counts the number of unique persons for specific events. It then aggregates the counts for each event type and campaign, grouping them by year, month, campaign ID, and campaign name. The final result is ordered by year, month, and campaign name.
klaviyo
Monthly Campaign Status Breakdown by List
Generates a monthly breakdown of campaign status for a specific integration with Klaviyo. It retrieves the number of campaigns, the number of campaigns sent, the number of campaigns in draft and scheduled status, as well as other campaign statuses if needed. Additionally, it includes the list name, the number of lists used, and the last sent date. The results are grouped by campaign month, list name, and template name, and ordered in descending order of the campaign month.
klaviyo
Monthly Flow Events
Generates a report of monthly flow events from the Klaviyo integration. It calculates the total number of events triggered and the number of distinct persons triggered for each flow in a given month. The result includes the month, flow ID, flow name, flow status, trigger type, trigger filter, customer filter, total events triggered, and distinct persons triggered, ordered by month and flow name.
klaviyo
Email Events
Retrieves various event details from the Klaviyo integration, including the event ID, timestamp, event name, custom properties, Klaviyo properties, UUID, person ID, metric ID, campaign ID, flow ID, flow, attributed event ID, message ID, send cohort, send timestamp, and order ID (if the event name is "Placed Order"). The results are ordered by the event timestamp in descending order.
klaviyo
Events Flow Campaign Report
Retrieves data from the Klaviyo integration to generate an Events Flow Campaign Report. It combines information from the events, campaign, and flow tables to provide details such as event timestamps, event names, custom properties, Klaviyo properties, metric IDs, campaign IDs, person IDs, flow IDs, attributed event IDs, message IDs, send cohorts, send timestamps, order IDs, flow names, flow statuses, campaign subjects, and campaign sent timestamps. The final result is sorted in descending order based on the event timestamps.
instagram-business

Instagram business

instagram-business
Profile Overview Report
Profile overview report for Instagram Business integration. It combines data from different tables to calculate average follows count, average followers count, average profile views, and average website clicks for each user on a monthly basis. The results are then ordered by month.
instagram-business
User Engagement and Outreach Report
Generates a report on user engagement and outreach for an Instagram Business integration. It combines data from various tables to calculate average follower count, profile views, website clicks, like count, comments count, feed impressions, and feed reach on a monthly basis. The result is sorted by month and user ID.
instagram-business
Media Performance Report
Generates a media performance report for Instagram Business integration. It combines metrics from the media_history and media_insights tables to provide a comprehensive view of monthly media performance. The report includes metrics such as total media posts, likes, comments, carousel engagement, impressions, reach, reel likes, plays, feed engagement, impressions, and reach.
instagram-business
Media Insights Deep Dive
Retrieves various metrics for different types of media posts (carousel, reel, story, feed). It also calculates engagement rates for each type of media. The template joins the media data with the media history table to include additional information such as caption, timestamp, media type, likes count, and comments count. The final result is sorted by month and media posted date in descending order.
instagram-business
Growth Analysis
Calculates the growth rates of various metrics for an Instagram Business account. It retrieves monthly data on average followers, average follows, total feed engagement, total carousel engagement, and total reel likes. It then calculates the growth rate percentages for each metric compared to the previous month. The results are sorted by month in descending order.
instagram-business
Engagment Metric Analysis
Calculates various engagement metrics for Instagram Business accounts on a monthly basis. It retrieves data such as the total number of media posts, likes, comments, feed engagement, carousel engagement, reel likes, reel comments, story replies, and story exits. Additionally, it calculates average engagement per post metrics, including average likes, comments, feed engagement, carousel engagement, reel likes, and reel comments per post. The results are ordered by month in descending order.
clickup

Clickup

clickup
Team Overview Report
Generates a team overview report by retrieving data from the ClickUp integration. It calculates the total number of tasks, total time spent, task status, and task count for each team. The report includes team ID, name, color, total tasks, total time spent, task status, and tasks in each status. The results are ordered by team ID.
clickup
Task Dependency Analysis
Performs a task dependency analysis in ClickUp. It retrieves information about task dependencies, including task names, statuses, and dependent task details. It also calculates the total number of dependencies for each task and determines if a task is a potential blocker based on its status and the status of its dependent tasks. The results are sorted by task ID and dependent task ID.
clickup
User Activity and Performance Report
Generates a user activity and performance report by integrating with ClickUp. It retrieves information such as the total number of tasks, tasks completed, tasks overdue, total time spent, task priority, task status, and task count by priority and status for each user. The results are ordered by user ID.
clickup
Monthly Time Tracking and Billing Report
Generates a monthly time tracking and billing report using data from ClickUp. It calculates the billable and non-billable time for each user, as well as the total time spent. The report also includes the billable amount, calculated based on a hypothetical rate of $50 per hour.
clickup
Task Checklist Progress
Generates a report on the progress of task checklists in ClickUp. It retrieves the total number of checklist items, resolved items, and unresolved items for each task. It calculates the completion percentage based on the resolved items and total checklist items. The results are sorted by completion percentage and task ID.
Weld logo

Tired of scattered data? Build your data warehouse with Weld in minutes with our powerful ELT, SQL Transformations, Reverse-ETL and AI Assistant - connected to 100+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
GDPR logoSOC2
© 2024 Weld. All rights reserved.