queries.sql

CREATE VIEW ritten AS
SELECT 
	fa.id,
	fa.date, datetime(fa.date, 'unixepoch', 'localtime') AS date_dt, fo.date AS orig_date,
	fa.name,
	fa.type,
	fa.description,
	fa.elapsed_time,
	fa.distance,
	fa.bpm_max,
	fa.comparing_try,
	fa.commute,
	fa.private_note,
	fa.gear,
	fa.file,
	fa.athlete_weight,
	fa.bicycle_wieght,
	fa.elapsed_time2,
	fa.moving_time,
	fa.distance2,
	fa.speed_max,
	fa.speed_avg,
	fa.ascend_total,
	fa.descend_total,
	fa.lowest_altitude,
	fa.highest_altitude,
	fa.slope_max,
	fa.slope_avg,
	fa.slope_avg_up,
	fa.slope_avg_down,
	fa.rpm_max,
	fa.rpm_avg,
	fa.bpm_max2,
	fa.bpm_avg2,
	fa.power_max,
	fa.power_avg,
	fa.calories,
	fa.temperature_max,
	fa.temperature_avg,
	fa.comparing_try2,
	fa.work_total,
	fa.runner_sessions_amount,
	fa.time_ascending,
	fa.time_descending,
	fa.other_time,
	fa.experienced_intensity,
	fa.type2,
	fa.start,
	fa.power_avg_weighted,
	fa.power_sensor_amount,
	fa.preference_for_experienced_exercise,
	fa.comparing_try_for_experienced,
	fa.commute2,
	fa.total_lifted_weight,
	fa.is_uploaded,
	fa.distance_adjusted_to_slope,
	fa.weather_timestamp,
	fa.weather_circumstances,
	fa.outside_temperature,
	fa.perceived_temperature,
	fa.dew_point,
	fa.humidity,
	fa.atmospheric_pressure,
	fa.wind_speed,
	fa.wind_speed_max,
	fa.wind_direction,
	fa.precipitation,
	fa.dusk,
	fa.dawn,
	fa.moon,
	fa.bicycle,
	fa.gear2,
	fa.precipitation_probabilty,
	fa.precipitation_type,
	fa.cloud_cover,
	fa.vision,
	fa.uv_index,
	fa.ozone,
	fa.jumps_count,
	fa.grit_total,
	fa.average_flow,
	fa.flagged,
	fa.speed_avg_from_elapsed_time,
	fa.distance_unpaved,
	fa.distance_new_roads,
	fa.distance_new_roads_unpaved,
	fa.activity_count,
	fa.steps,
	fa.co2_saved,
	fa.swimming_pool_length,
	fa.training_impact,
	fa.intensity,
	fa.avg_speed_on_flat_terrain,
	fa.time_from_timer,
	fa.cyclus_count,
	fa.media
	FROM frombulkexport_activities fa JOIN frombulkexport_activities_orig fo ON fa.id = fo.id 
	;
	
SELECT fa.gear, COUNT(fa.gear) FROM frombulkexport_activities fa GROUP BY fa.gear;

SELECT id, date, date_dt, name, "type", distance, description, elapsed_time, elapsed_time2, speed_avg FROM ritten WHERE gear = 'DF 15'