Generating a Materialized View from a key value time table.

reporting_query.sql

create table 
        mv_simple_statistics_data_most_recent 
        select
            subject,
            `key`,
            value,
            previous_value,
            value-previous_value as value_delta,
            `date`,
            previous_date,
            datediff(`date`, previous_date) as days,
            round((value-previous_value) / if(datediff(`date`, previous_date) = 0, 1, datediff(`date`, previous_date)), 1) as change_per_day
            from 
                 (

                    select 
                       s1.`subject`, 
                       s1.`key`, 
                       s1.`value`, 
                       (
                            select 
                                s3.`value` as `previous_value`
                            from  
                                simple_statistics_data s3
                           where 
                                 s1.subject = s3.subject and s1.key = s3.key
                           order by s3.`date` desc
                           LIMIT 1,1
                       ) as previous_value,
                       s1.`date`,
                       (
                            select 
                                s3.`date` as `previous_date`
                            from  
                                simple_statistics_data s3
                           where 
                                 s1.subject = s3.subject and s1.key = s3.key
                           order by s3.`date` desc
                           LIMIT 1,1
                       ) as previous_date
                    from 
                        simple_statistics_data s1 
                    inner join 
                        (
                            select `subject`, `key`, max(`date`) as `date` 
                            from 
                                simple_statistics_data 
                            group by `subject`, `key`
                        ) s2
                        on s1.subject = s2.subject and s1.key = s2.key and s1.date = s2.date
                ) s4;