---
# System prepended metadata

title: Querys para extração de métricas da integração com a casa do produto.
tags: [Querys, ' Casa do Produto']

---

---
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:
```sql=
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:
![](https://i.imgur.com/Xo9PNrV.png)

---
#### 2. Produtos afetados por tipo de atributo (filtro por data):
params: 
- inicial_date: 'DD-MM-YYYY', 
- final_date: 'DD-MM-YYYY',
- backend_types: ['type', ...]
```sql=
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:
![](https://i.imgur.com/jFJ3G9Z.png)

---
#### 3. Detalhes dos produtos afetados (filtro por data):
params: 
- inicial_date: 'DD-MM-YYYY', 
- final_date: 'DD-MM-YYYY',
- backend_types: ['type', ...]
```sql=
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:
![](https://i.imgur.com/jYjaysz.png)

---
#### 4. Quantidade de produtos preenchidas por atributo (filtro por data):
params: 
- inicial_date: 'DD-MM-YYYY', 
- final_date: 'DD-MM-YYYY'
```sql=
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:
![](https://i.imgur.com/4CBR4Va.png)

---
#### 5. Atributos de tabela nutricional: 
```sql= 
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:
![](https://i.imgur.com/lXADd8B.png)

