---
tags: Querys, Casa do Produto
---

# Querys para extração de métricas da integração com a casa do produto.

#### 1. Média de atributos atualizados por produto:

SELECT 
	ROUND(AVG(count(*))) AS average_attributes_per_product
FROM 
	ATTRIBUTE_VALUE av
INNER JOIN 
	"ATTRIBUTE" att
ON 
	av.ATTRIBUTE_ID = att.ID
INNER JOIN 
	PRODUCT p
ON 
	p.ID = av.PRODUCT_ID
WHERE 
	att.ORIGIN = 'casaProdto'
GROUP BY 
	p.SKU;

#### Result:

---
#### 2. Produtos afetados por tipo de atributo (filtro por data):
params:
- inicial_date: 'DD-MM-YYYY',
- final_date: 'DD-MM-YYYY',
- backend_types: ['type', ...]

SELECT 
	count(av.PRODUCT_ID) AS total_products,
	TO_CHAR(av.LAST_UPDATED, 'DD-MM-YYYY') AS last_update
FROM 
	ATTRIBUTE_VALUE av 
INNER JOIN 
	"ATTRIBUTE" att 
ON 
	av.ATTRIBUTE_ID = att.ID 
WHERE 
	att.ORIGIN = 'casaProdto'
AND
	av.LAST_UPDATED BETWEEN TO_DATE(:inicial_date, 'DD-MM-YYYY') AND TO_DATE(:final_date, 'DD-MM-YYYY') 
AND 
	att.BACKEND_TYPE IN (:BACKEND_TYPES)
GROUP by 
	TO_CHAR(av.LAST_UPDATED, 'DD-MM-YYYY');

#### Result:

---
#### 3. Detalhes dos produtos afetados (filtro por data):
params:
- inicial_date: 'DD-MM-YYYY',
- final_date: 'DD-MM-YYYY',
- backend_types: ['type', ...]

SELECT
	p.SKU,
	av.ATTRIBUTE_CODE,
	TO_CHAR(av.LAST_UPDATED, 'DD-MM-YYYY HH24:MI:SS') AS updated_at,
	att.BACKEND_TYPE
FROM 
	ATTRIBUTE_VALUE av 
INNER JOIN 
	"ATTRIBUTE" att 
ON 
	av.ATTRIBUTE_ID = att.ID 
INNER JOIN 
	PRODUCT p
ON 
	p.ID = av.PRODUCT_ID
WHERE 
	att.ORIGIN = 'casaProdto'
AND
	av.LAST_UPDATED BETWEEN TO_DATE(:inicial_date, 'DD-MM-YYYY') AND TO_DATE(:final_date, 'DD-MM-YYYY')
AND 
	att.BACKEND_TYPE IN (:BACKEND_TYPES)
ORDER BY 
	p.SKU;

#### Result:

---
#### 4. Quantidade de produtos preenchidas por atributo (filtro por data):
params:
- inicial_date: 'DD-MM-YYYY',
- final_date: 'DD-MM-YYYY'

SELECT
    COUNT(*) qtd,
    av.attribute_code,
    LISTAGG(p.sku, ', ') WITHIN GROUP(ORDER BY p.sku) skus
FROM
	product p
INNER JOIN 
	attribute_value av 
ON 
	p.id = av.product_id
INNER JOIN 
	attribute a 
ON 
	av.attribute_id = a.id
LEFT JOIN 
	attribute_group_item agi 
ON 
	agi.attribute_id = a.id
LEFT JOIN 
	attribute_group ag 
ON 
	ag.id = agi.attribute_group_id
LEFT JOIN 
	attribute_set	ast 
ON 
	ast.id = ag.attribute_set_id
WHERE
    a.origin = 'casaProdto'
AND 
	ast.name 
NOT IN 
	( 'DefaultNew', 'Marketplace' )
AND 
	av.last_updated BETWEEN TO_DATE(:inicial_date, 'DD-MM-YYYY') AND TO_DATE(:final_date, 'DD-MM-YYYY')
GROUP BY
	av.attribute_code
ORDER BY
    qtd DESC;

#### Result:

---
#### 5. Atributos de tabela nutricional:

SELECT
    a.code,
    COUNT(1) AS qtd_atributos
FROM
         attribute_value av
    INNER JOIN attribute a ON a.id = av.attribute_id
    INNER JOIN product   p ON p.id = av.product_id
WHERE
        a.origin = 'casaProdto'
    AND a.code LIKE 'infnutric%'
GROUP BY
    a.code;

#### Result: