How to use the flowmachine.utils.make_where function in flowmachine

)

        returning_time_columns = ", ".join(
            [x.split(" AS ")[-1] for x in self.redaction_target.time_cols]
        )
        # We now need to group this table by the relevant columns in order to
        # get a count per region
        sql = f"""
            WITH tne AS (SELECT
                {', '.join(self.redaction_target.spatial_unit.location_id_columns)},
                {', '.join(self.redaction_target.time_cols)},
                count(*) AS value,
                count(distinct subscriber) > 15 AS safe_agg
            FROM
                ({self.redaction_target.unioned.get_query()}) unioned
            {make_where(self.redaction_target.direction.get_filter_clause())}
            GROUP BY
                {', '.join(groups)})
                
            SELECT {returning_columns}, 
                {returning_time_columns},
                value
            FROM tne NATURAL JOIN
            (SELECT {returning_columns} FROM tne 
            GROUP BY {returning_columns}
            HAVING every(safe_agg)
            ) _
        """

        return sql

# list of columns that we want to group by, these are all the time
        # columns, plus the location columns
        groups = [
            x.split(" AS ")[0] for x in self.time_cols
        ] + self.spatial_unit.location_id_columns

        # We now need to group this table by the relevant columns in order to
        # get a count per region
        sql = f"""
            SELECT
                {', '.join(self.spatial_unit.location_id_columns)},
                {', '.join(self.time_cols)},
                count(*) AS value
            FROM
                ({self.unioned.get_query()}) unioned
            {make_where(self.direction.get_filter_clause())}
            GROUP BY
                {', '.join(groups)}
        """

        return sql

def _make_query(self):
        loc_cols = ", ".join(self.spatial_unit.location_id_columns)
        where_clause = make_where(self.direction.get_filter_clause())

        return f"""
        SELECT subscriber, {loc_cols}, {self.statistic}(duration) as value 
        FROM ({self.unioned_query.get_query()}) u
        {where_clause}
        GROUP BY subscriber, {loc_cols}

def _make_query(self):
        return f"""
        SELECT subscriber, COUNT(*) as value FROM
        ({self.unioned_query.get_query()}) u
        {make_where(self.direction.get_filter_clause())}
        GROUP BY subscriber

def _make_query(self):

        filters = [self.direction.get_filter_clause("A")]
        if self.exclude_self_calls:
            filters.append("A.subscriber != A.msisdn_counterpart")
        on_filters = make_where(filters)

        sql = f"""
        SELECT
            U.subscriber AS subscriber,
            {self.statistic}(D.value) AS value
        FROM
            (
                SELECT A.subscriber, A.location_id AS location_id_from, B.location_id AS location_id_to FROM
                ({self.unioned_from_query.get_query()}) AS A
                JOIN ({self.unioned_to_query.get_query()}) AS B
                ON A.id = B.id AND A.outgoing != B.outgoing {on_filters}
            ) U
        JOIN
            ({self.distance_matrix.get_query()}) D
        USING (location_id_from, location_id_to)
        GROUP BY U.subscriber

def _make_query(self):
        loc_cols = ", ".join(self.spatial_unit.location_id_columns)

        where_clause = make_where(self.direction.get_filter_clause())

        return f"""
        SELECT subscriber, {self.statistic}(events) AS value
        FROM (
            SELECT subscriber, {loc_cols}, COUNT(*) AS events
            FROM ({self.unioned_query.get_query()}) U
            {where_clause}
            GROUP BY subscriber, {loc_cols}

def _make_query(self):

        filters = [self.direction.get_filter_clause()]

        if self.exclude_self_calls:
            filters.append("subscriber != msisdn_counterpart")
        where_clause = make_where(filters)

        on_clause = f"""
        ON {'U.subscriber' if self.subscriber_identifier == 'msisdn' else 'U.msisdn'} = R.subscriber
        AND  U.msisdn_counterpart = R.msisdn_counterpart
        """

        sql = f"""
        SELECT subscriber, AVG(reciprocal::int) AS value
        FROM (
            SELECT U.subscriber, COALESCE(reciprocal, FALSE) AS reciprocal
            FROM (
                SELECT *
                FROM ({self.unioned_query.get_query()}) U
                {where_clause}
            ) U
            LEFT JOIN (

def _make_query(self):
        location_columns = self.spatial_unit.location_id_columns
        sql = f"""
        WITH unioned_table AS ({self.unioned_query.get_query()})
        SELECT {', '.join(location_columns)}, sum(introverted::integer)/count(*)::float as value FROM (
            SELECT
               {', '.join(f'A.{c} as {c}' for c in location_columns)},
               {' AND '.join(f'A.{c} = B.{c}' for c in location_columns)} as introverted
            FROM unioned_table as A
            INNER JOIN unioned_table AS B
                  ON A.id = B.id
                     AND A.outgoing != B.outgoing
                     {make_where(self.direction.get_filter_clause(prefix="A"))}
        ) _
        GROUP BY {', '.join(location_columns)}
        ORDER BY {', '.join(location_columns)}
        """

        return sql

def _make_query(self):
        where_clause = make_where(self.direction.get_filter_clause())

        sql = f"""
        SELECT
            subscriber,
            AVG(nocturnal)*100 AS value
        FROM (
            SELECT
                subscriber,
                CASE
                    WHEN extract(hour FROM datetime) >= {self.hours[0]}
                      OR extract(hour FROM datetime) < {self.hours[1]}
                    THEN 1
                ELSE 0
            END AS nocturnal
            FROM ({self.unioned_query.get_query()}) U
            {where_clause}

def _absolute_freq_query(self):

        return f"""
        SELECT subscriber, COUNT(*) AS absolute_freq FROM
        ({self.unioned_query.get_query()}) u
        {make_where(self.direction.get_filter_clause())}
        GROUP BY subscriber, EXTRACT( {self.phase} FROM datetime )
        HAVING COUNT(*) > 0