Here's an SQL query to extract data from two datasets: leads (crm_lead) and lead custom fields (crm_lead_uf). You can copy it and see how it works. Replace UF_CRM_"field ID" with your custom field before running the query.

SELECT  
   crm_lead.ID AS "Lead ID",
   crm_lead.TITLE AS "Lead name",
   crm_lead.DATE_CREATE AS "Lead creation date",
   crm_lead.OPPORTUNITY AS "Lead amount",
   crm_lead_uf.UF_CRM_1701433962427 AS "Custom field"
FROM 
   crm_lead
INNER JOIN 
   crm_lead_uf ON crm_lead.ID = crm_lead_uf.LEAD_ID
WHERE 
   crm_lead_uf.UF_CRM_1701433962427 IS NOT NULL 
   AND crm_lead_uf.UF_CRM_1701433962427 <> ''

SELECT: Chooses which data to extract. For example, the SELECT crm_lead query.ID AS "Lead ID" part does the following:

• Selects the lead ID from the crm_lead dataset
• Assigns it the name Lead ID
• Displays the result in a separate column

FROM: Specifies the main table for the query, such as the crm_lead table containing lead information.

INNER JOIN: Connects different tables to combine their data. For example, crm_lead_uf ON crm_lead.ID = crm_lead_uf.LEAD_ID links standard and custom fields by lead ID.

WHERE: Filters the query results to show the rows where the custom field is not empty.

To see how many leads have turned into deals, extract data from two datasets: leads (crm_lead) and deals (crm_deal). Here's an SQL query that shows converted leads and their corresponding deals from the past 30 days. You can copy it and see how it works.

SELECT
   crm_lead.ID AS "Lead ID",
   crm_lead.TITLE AS "Lead name",
   crm_lead.DATE_CREATE AS "Lead creation date",
   crm_deal.ID AS "Deal ID",
   crm_deal.TITLE AS "Deal name",
   crm_deal.DATE_CREATE AS "Deal creation date"
FROM
   crm_lead
INNER JOIN
   crm_deal ON crm_lead.ID = crm_deal.LEAD_ID
WHERE
   crm_deal.DATE_CREATE >= date_add('day', -30, current_date)

SELECT: Chooses which data to extract. For example, the SELECT crm_lead query.ID AS "Lead ID" part does the following:

• Selects the lead ID from the crm_lead dataset
• Assigns it the name Lead ID
• Displays the result in a separate column

FROM: Specifies the main table for the query, such as the crm_lead table containing lead information.

INNER JOIN: Connects different tables to combine their data. For example, crm_lead_uf ON crm_lead.ID = crm_lead_uf.LEAD_ID does the following:

• INNER JOIN crm_deal connects the crm_lead table to the crm_deal table
• ON crm_lead.ID = crm_deal.LEAD_ID adds a connection condition: the lead ID in the crm_lead (ID) table must match the lead ID in the crm_deal (LEAD_ID) table. This lets you connect each converted lead to its corresponding deal.

WHERE: Filters the query results to meet the specified conditions. For example, crm_deal.DATE_CREATE >= date_add('day', -30, current_date) does the following:

• Calculates the date 30 days ago from today using the date_add ('day', -30, current_date) function
• Compares the deal creation date (crm_deal.DATE_CREATE) to the calculated date
• Selects only deals created in the last 30 days. This shows leads converted into deals in the past month.