-- View: "v_cast_ranking"

DROP VIEW v_cast_ranking;

CREATE VIEW v_cast_ranking AS
SELECT
    ci.castcd,
    ca.shopdiv,
    ca.castdiv,
    ca.castnm,
	COALESCE(o.count,0) as count,
    sum(a.refercount) as refercount

FROM t_access a
    INNER JOIN t_cast_item ci
      INNER JOIN t_cast ca
        ON ca.castcd = ci.castcd
    ON ci.itemcd = a.itemcd
    LEFT JOIN
	(select itemcd, count(*) as count from t_order_detail where typediv = 1 group by itemcd) o
	ON o.itemcd=ci.itemcd


WHERE
    ca.delflg = FALSE
    AND a.accessdate >= CURRENT_DATE-7
GROUP BY ci.castcd, ca.shopdiv, ca.castdiv, ca.castnm, o.count;