---
tags: Querys, Preço comparativo
---

# Querys para extração de infos sobre preço comparativo.

### Total de produtos de peso variável

SELECT
   prods.SKU,
   prods.NAME,
   av.int_value AS weightIndex
FROM
     (
        SELECT DISTINCT
            p.id,
            p.SKU,
            p.NAME
        FROM
             product p
      	INNER JOIN 
      		product_cache pc 
      	ON 
      		p.id = pc.product_id
       	AND 
       		pc.status = 1
    ) prods
    INNER JOIN 
    	attribute_value av 
	ON 
		av.product_id = prods.id
WHERE
    av.store_id IS NULL
    AND av.attribute_code = 'weightIndex';

### Total de produtos

SELECT
    COUNT(*)
FROM
    (
        SELECT DISTINCT
            p.id
        FROM
                 product p
            INNER JOIN product_cache pc ON p.id = pc.product_id
                                           AND pc.status = 1
    ) prods
    

### Extração

   SELECT
    main.sku,
    main.nome,
    main.loja,
    main.preco,
    main.indice_peso,
    main.preco_comparativo,
    sub.cod_conversao_individual
FROM (
    SELECT
        p.ID,
        p.sku,
        p.NAME AS nome,
        s.NAME AS loja,
        MAX(CASE WHEN av.ATTRIBUTE_CODE = 'price' THEN av.DOUBLE_VALUE END) AS preco,
        MAX(CASE WHEN av.ATTRIBUTE_CODE = 'weightIndex' THEN av.int_value END) AS indice_peso,
        MAX(CASE WHEN av.ATTRIBUTE_CODE = 'comparativePrice' THEN av.STRING_VALUE END) AS preco_comparativo
    FROM
        STORE s
    LEFT JOIN ATTRIBUTE_VALUE av ON
        av.STORE_ID = s.ID
    LEFT JOIN PRODUCT p ON
        av.PRODUCT_ID = p.ID
    WHERE
        av.ATTRIBUTE_CODE IN ('price', 'weightIndex', 'comparativePrice')
        AND av.END_DATE IS NULL
    GROUP BY
        p.ID, s.NAME, p.SKU, p.NAME
) main
LEFT JOIN (
    SELECT
        av.PRODUCT_ID,
        LISTAGG(av.TEXT_VALUE, ', ') WITHIN GROUP (ORDER BY av.TEXT_VALUE) AS cod_conversao_individual
    FROM
        ATTRIBUTE_VALUE av
    WHERE
        av.ATTRIBUTE_CODE = 'codigoConversaoUnidade'
    GROUP BY
        av.PRODUCT_ID
) sub ON main.id = sub.PRODUCT_ID
ORDER BY 
    main.preco_comparativo;