-- View: "v_item_ranking"

DROP VIEW v_item_ranking;

CREATE OR REPLACE VIEW v_item_ranking AS
SELECT
    i.itemcd,
    i.itemnm,
    COALESCE(o.count,0) as count,
    sum(a.refercount) as refercount

FROM t_access a
    INNER JOIN t_item_basic_attr i
    ON i.itemcd = a.itemcd
    LEFT JOIN
	(select itemcd, count(*) as count from t_order_detail where typediv = 1 AND preorderflg = FALSE AND delflg = FALSE group by itemcd) o
	ON o.itemcd=a.itemcd

WHERE
    i.delflg = FALSE
    AND a.accessdate >= CURRENT_DATE-7
GROUP BY i.itemcd, i.itemnm, o.count;